| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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!! |
|
#2
|
|||
|
|||
|
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!! |
|
#3
|
|||
|
|||
|
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!! |
|
#4
|
|||
|
|||
|
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!! |
|
#5
|
|||
|
|||
|
"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!! |
|
#6
|
|||
|
|||
|
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!! |
|
#7
|
|||
|
|||
|
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!! |
|
#8
|
|||
|
|||
|
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy formula referencing sheet name to another sheet | Excel Worksheet Functions | |||
| is there anyway in an excel macro to reorder the sheet tabs from left to right? | Excel Worksheet Functions | |||
| data from one sheet to several in sequential order! | New Users to Excel | |||
| How do I change the font color of my sheet tabs? | Excel Discussion (Misc queries) | |||
| Naming & renaming a sheet tab | Excel Worksheet Functions | |||
Linear Mode
