Why is this a circular reference?

C

CK

Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))>0,INDIRECT(ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can anyone
explain to me why?
 
F

FSt1

hi,
guessing here but...
if references are omited from Row() or Column() then excel assumed that
reference is the row or column that the functions appears in which would
create a circular reference. seems that you will need references to distance
the formula from the data.

Regards
FSt1
 
T

T. Valko

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))>0

It refers to the cell that the formula is in thus being a circular
reference.

Biff
 
C

CK

Now I understand. But the interesting thing is, the formula

=INDIRECT(ADDRESS(ROW(),COLUMN()))>0

actually works in conditional formatting.
 
T

T. Valko

CF is different from a worksheet cell formula. The formula used in CF does
not actually reside in the cell like a worksheet formula.

Biff
 
C

CK

Thanks a lot mate!!!

T. Valko said:
CF is different from a worksheet cell formula. The formula used in CF does
not actually reside in the cell like a worksheet formula.

Biff
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to slove: Circular Reference Warning 1
offset formula causing Circular reference 0
Circular Calculation with TREND Function? 7
Circular reference, Indirect, and Sum 3
Circular reference, Indirect, and Sum 3
summing across worksheets conditionally and being able to copy for 4
Copying linked sheet 2
find/verify circular references 3

Top