using indirect references

[johngio]

hi all,

I'm trying to use MATCH across a range of figures, and I want the first row number to be that which is in another cell (the number 270 at the moment, but it will change). Here is my formula:

=MATCH(0,'Sheet1!BY(INDIRECT("$Q$59")):BY4000, 0)

I'm trying to find the first zero after the series of numbers (which works fine when I enter 270 instead of (INDIRECT("$Q$59")). I'm pretty sure my layout is wrong.

I keep getting a #NAME? error.

Can someone please tell me the correct way to lay this out?

Thanks

 

[johngio]

If any one has any idea; please let me know; even if it's a different formula - I have a cell that will generate a number, then I need to put that number into an array such as A 'the number I will generate' :A4000



Thanks

 

[Fergus]

Hi John,

Try: =MATCH(0,INDIRECT("Sheet1!BY"&$Q$59):INDIRECT("Sheet1!BY4000"),0)

This assumes your reference cell Q59 is on Sheet2

 

[Aladin Akyurek]

If 270 stands for row 270...

=MATCH(0,INDEX(Sheet1!$BY:$BY,$O$59):Sheet1!$BY$4000,0)

 

[johngio]

Hooray!!!!

You guys are legends!!!!