OR(INDIRECT(....ROW() array problem

Hi All

Just been trying to compare a cell value to all cells below that one in a list. The list can vary in length, so I use COUNTA($A:$A) to provide the right figure.

The formula I have used, on a set of dummy data, returns value error when I reference the ROW()+1 (to define the cell below the reference), yet is fine if I hard code in a number, say 3 or even refer it to a third cell which contains ROW()+1

To explain a bit more, this works:

={IF(OR(INDIRECT("$a"&E1&":$a$"&COUNTA(A:A))="d"),"Y","N")} where E1 contains =ROW()+1

and this works:


={IF(OR(INDIRECT("$a"&6&":$a$"&COUNTA(A:A))="d"),"Y","N")}


but this doesn't:

={IF(OR(INDIRECT("$a"&(ROW()+1)&":$a$"&COUNTA(A:A))="d"),"Y","N")}


DOes anyone know why this third one (which is the ideal solution) does not work? I have tried using VALUE and TEXT to force the format with no joy, even ROW(A:A)

Many thanks in advance


Andy

Thanks for the speedy answers guys, I'll give the suggestions a go. I didn't think of using INDEX....!


Regards


Andy