Dynamic reference to another tab name

[Guest]

Currently I have a cell with this formula:
=A2 & !D6

Cell A2 contains the name of another tab in the same book and I wish to
display the value from cell D6 of that sheet. Clearly the above syntax is
incorrect but what should it be?

Thanks in advance.

 

[Bernard Liengme]

Look at the INDIRECT function
best wishes

 

[Biff]

Try this:

=INDIRECT("'"&A2&"'!D6")

Biff

 

[Guest]

I need this way of referencing to other sheets (TABs). I tried it and it
works BUT the D6 reference is no more changing when I copy it over a huge
range, for instance changing to D5, D4. Any medicine for this?

Morten

"Biff" skrev:

 

[Don Guillett]

This will still use the same sheet but a row down when copied down
=INDIRECT($A$2&"!"&"B"&ROW(A3))

 

[Guest]

If i understand the question correctly you need to hard referance D6 by
adding $'s.

=INDIRECT("'"&A2&"'!$D$6")

cheers