INDIRECT and INDEX error

[aaron1000]

Hi Folks, I am migrating a number of formulas to make use of the INDIRECT funtion (see history here http://www.mrexcel.com/forum/showthread.php?t=556583).

One formula is giving me difficulties. It grabs the last value of a column in a worksheet and displays it in a 'calcs' worksheet.

I have it working correctly when pasted into individual worksheets:
=INDEX(C:C,MATCH(9.99999999999999E+307,C:C))

but when I move it to my 'calcs' worksheet and add in the indirect funtion to reference the original worksheet, I get the old #REF error which I believe its telling me it can't resolve the location:
=INDEX(INDIRECT("'"&ROW()&"'!C"):INDIRECT("'"&ROW()&"'!C"),MATCH(9.99999999999999E+307,INDIRECT("'"&ROW()&"'!C"):INDIRECT("'"&ROW()&"'!C")))

Appreciate any help on this.

cheers
Aaron

 

[Andrew Poulsom]

What is this supposed to return?

INDIRECT("'"&ROW()&"'!C")

If in A1 you would get:

INDIRECT('1'!C)

which is not a valid reference.

 

[aaron1000]

Hi, so what your saying is that where '1'!C is referenced with INDIRECT("'"&ROW()&"'!C") isn't going to work because INDIRECT function can't be used as a column reference...only as a cell reference.

In that case is there another way I can increment the worksheet reference sequentially by one so that when I copy the formula down in the 'calcs' worksheet it automatically increments. For example the end result I want is:

=INDEX('1'!C:C,MATCH(9.99999999999999E+307,'1'!C:C))
=INDEX('2'!C:C,MATCH(9.99999999999999E+307,'2'!C:C))
=INDEX('3'!C:C,MATCH(9.99999999999999E+307,'3'!C:C))
.
.
.
=INDEX('250'!C:C,MATCH(9.99999999999999E+307,'250'!C:C))


cheers
Aaron

 

[Andrew Poulsom]

Oh I see:

=INDEX(INDIRECT("'"&ROW()&"'!C:C"),MATCH(9.99999999999999E+307,INDIRECT("'"&ROW()&"'!C:C")))

 

[aaron1000]

Awesome......I can see where I had the syntax not quite right.

Thanks
Aaron