Referencing Sheet Tabs
 

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!!

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!!

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!!

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!!

"DB" wrote:

David I would like 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!!

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!!

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!!

Bob this work just fine. Thanks so much to everyone that helped.

DB

"Bob Phillips" wrote:

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!!