Circular reference when using Indirect Function... Not sure why

[RobClay]

Preface: I am not expert, but have been viewing the message boards, especially here for some advice on a spreadsheet I have been building.

I have a formula that will review an array (Values in E4 through E100) and let me know if it is the first time a value (In Column E) is seen and display a 1, or a repeat of a previous value, and display an "R". This is the formula that would be used in A7:
=IF(ROW(INDEX(($E$4:$E$100),+MATCH(E7,$E$4:$E$100,0),0))=ROW(),1,"R")

This works.

Now I want to use the indirect function so that I can just put in the number of rows in the array:

In Cell C1 I put in the value 100.
In A7:
=IF(ROW(INDEX(INDIRECT("$E$4:$E$"&$C$1),+MATCH(E7,INDIRECT("$E$4:$E$"&$C$1),0),0))=ROW(),1,"R")

This gives me a circular reference. It's probably obvious, but I am unsure why. Any suggestions or a better formula?

Thanks!

 

[Marcelo Branco]

Hi and welcome to MrExcel Forum,

Considering that the values begin at E4, maybe you can simply use this formula in A4

=IF(MATCH(E4,E:E,0)=ROW(),1,"R")
copy down

M.

 

[RobClay]

Eureka... That was much simpler. Thanks!

 

[Marcelo Branco]

You are welcome and thanks for the feedback!

M.

 

[RobClay]

Carrying this further... this has me discovering other circular references... all of which 'work' and I get the results I seek...

In Cell A17, I want to count the following 4 cells under it to see if they have a "1" in them. I use the following:
=COUNTIF(A18:A21,1) and it returns 4 (as expected assuming all A18, A19, A20, A21 each have 1 in them.

Now I want to use the indirect function to get the same results, so in A17 I use:
COUNTIF(INDIRECT("A"&ROW()+1&":A21"),1) and it returns a 4, but also gives me a circular reference error.

Am I using indirect incorrectly?


(
As an aside:
In case one is wondering where this leads me, or why, I eventually will have a formula that does the following:
=COUNTIF(INDIRECT("A"&ROW()+1&":A"&ROW()+[Formula to Calculate Number of Rows I want to test]),1)
)

 

[RobClay]

Looking at others with similar problems, it seems as though the row() and indirect do not like each other.

 

[Marcelo Branco]

Some points:
1. You should avoid the use of volatile functions, like INDIRECT, whenever it's possible
See
Volatile Excel Functions -Decision Models

2. This formula in A17 worked perfectly for me, no circular references.
=COUNTIF(INDIRECT("A"&ROW()+1&":A21"),1)

M.