Hi,
I have the following formula that works:
={MATCH(TRUE,$E3:$J3="-",0)}
However, when I try to make the column and row references dynamic using INDIRECT then the formula returns an error:
={MATCH(TRUE,INDIRECT($N$1 & ROW() & ":" & $O$1 & ROW())="-",0)}
($N$1 contains the letter "E" and $O$1 contains the letter "J" and I am entering the formula using CTRL+SHIFT+ENTER)
Can INDIRECT and MATCH be used in this way? Or have I got the formula wrong in some way?
Thanks for any help in advance.
Graham
Have managed to get a bit further since posting this question. It seems to be the use of the ROW() function that is causing it to fail. I can separate the ROW() part out into a separate column and refer to it indirectly there, but is it possible to combine it into the original formula?
Hi Graham,
Try this version:
=MATCH(TRUE,INDEX(INDIRECT($N$1&ROW(),1):INDEX(INDIRECT($O$1&ROW()),1)="-",0))
