Try

=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!D5")

--
HTH

Bob Phillips

"DB" wrote in message
...
David when I type this formula =INDIRECT(A1&"!D5") in I get a #REF error.
When I "evaluate" the formula the wizard shows INDIRECT("Bob's Sales!D5").
So it apearrs that the formula is missing the critical single quote around
Bob's Sales.

Thanks!!

DB

"David Billigmeier" wrote:

Assume "Bob's Sales" is displaying in cell A1 when you select it from
the
drop down list (I assume you are just using a 'List' under data
validation to
achieve the drop down list?) Use this formula, entered in Sheet1 B2
(Note
the worksheet tabs have to correspond exactly to the name in the drop
down
list):

=INDIRECT(A1&"!D5")

Change the A1 reference to whichever cell is displaying your names.

--
Regards,
Dave


"DB" wrote:

David I would list the text "Bob's Sales" to come from the drop down
list. I
want the value in cell Sheet1 B2 to change each time another name is
selected
from the drop down list.

"David Billigmeier" wrote:

Use as the cell reference the following code:

='Bobs Sales'!D5


--
Regards,
Dave


"DB" wrote:

Is there a way to use the "value" of a name in a drop-down list to
reference
a sheet tab name in a formula?

A1 has a drop down list. When a name (Bob's Sales) is selected
from the
drop down list, B1 shows the value of cell D5 from sheet "Bob's
Sales".

Thanks for your help!!