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.

Look at the INDIRECT function
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Angus" wrote in message
...
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.

Try this:

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

Biff

"Angus" wrote in message
...
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.

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:

Try this:

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

Biff

"Angus" wrote in message
...
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.

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

"mus" wrote in message
...

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:

Try this:

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

Biff

"Angus" wrote in message
...
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.

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

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

cheers



"mus" wrote:


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:

Try this:

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

Biff

"Angus" wrote in message
...
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.