INDIRECT formula question

[zami]

Assume Sheet1 has indexed sheetnames in Column A.

=INDIRECT("'"&$A$3&"'!B4")


The above formula in A9 on Sheet1 will return the cell contents of B4 from the indexed sheetname in A3.

How would I adjust my formula if I wanted A9 on Sheet2 to populate from B4 of the sheetname in A3 on Sheet1?

TIA

 

[Andrew Poulsom]

Is it?

=INDIRECT("'"&Sheet1!$A$3&"'!B4")

 

[erik.van.geit]

zami,
did you try
=INDIRECT("'"&sheet1!$A$3&"'!B4")

kind regards,
Erik

 

[zami]

that's it. (you kids are quick!) mucho gracias.

 

[zami]

and along the same line...

Re the above formula, how can I make the INDIRECT formula in the target cell follow the source cell (B4); i.e., if a row is inserted above the source cell (B4 becomes B5), etc.? I wouldn't think $B$4 would apply here and in fact doesn't produce the desired result.

possible?

 

[erik.van.geit]

just prevent inserting rows
this looks to me the most obvious thing to do !

or at least only allow inserting rows with code (button) so you can update in the same run the indirect formula

To my feeling the nature of indirect has its disadvantages which you have to accept: there will be workarounds but that would lead to far to my sense.

Better prevent than cure, would be my objective.

best regards,
Erik