Indirect?

[VegasNath]

How can I change the A1 in the following formula to the cell reference that is stated in B1?

=IF(ISERROR(INDIRECT("'"&TEXT($A10,"dd-mm")&"'!A1"))=TRUE,0,INDIRECT("'"&TEXT($A10,"dd-mm")&"'!A1"))

[Don Wells]

Can you not replace "!A1" (including the quotation marks), with INDIRECT(B1)?

[mbarron]

Does this do what you want?

=IF(ISERROR(INDIRECT("'"&TEXT($A10,"dd-mm")&"'!" & B1))=TRUE,0,INDIRECT("'"&TEXT($A10,"dd-mm")&"'!" & B1))

[VegasNath]

=IF(ISERROR(INDIRECT("'"&TEXT($A10,"dd-mm")&"'!A1"))=TRUE,0,INDIRECT("'"&TEXT($A10,"dd-mm")&"'!A1"))

Finally got there:

=IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm")&"'!A1"))=TRUE,0,INDIRECT("'"&TEXT($A4,"dd-mm")&"'!"&TEXT(B$1,"")&""))


Thanks for the reply Don.

[VegasNath]

Thanks also Mike, i figured it out a short while ago after much frustration!

[VegasNath]

Out of curiosity:.....

A4 is a date (15/09/10)
W1 = E51:G51
W2 = E57:G57

W4 = =IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm")&"'!A1"))=TRUE,0,SUM(INDIRECT("'"&TEXT($A4,"dd-mm")&"'!"&TEXT(W$1,"")&""),INDIRECT("'"&TEXT($A4,"dd-mm")&"'!"&TEXT(W$2,"")&"")))

This works and provides the sum of E51:G51,E57:G57 from worksheet 15-09

However, Is it possible to concatenate the strings from W1:W2 into "E51:G51,E57:G57" (one cell) and therefore simplify the formula in W4 without having to sum various ranges separately?

[rory]

No, because you need the sheet name for each part. The TEXT function is unnecessary though around W1 and W2.

[VegasNath]

Thanks Rory.