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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!
>
>
>