Using Drop-Down Selections To Replace Worksheet Names in Formulas

grandpa_exceler · Feb 12, 2011

Using EXCEL 2007, Windows Vista (32).

I want to be able to place a reference in a formula to a cell containing a drop- down list so that the name in the drop-down list will replace the name (on another worksheet tab) to enable me to call up the data in the cell in the formula.
Say I have several worksheets whose tabs are John, Harry, Tom, and so on. The layout in each of these worksheets is identical, but the data in the cells may be unique to each worksheet.
I've generated another worksheet to be used in gathering the data from the same cells (we're talking about 280 cells of info) in each worksheet, one sheet at a time. So I placed formulas in the gathering worksheet table, containing a certain worksheet name and cell such as 'Harry'!$C9. Without using the Editing > Find & Select > Replace method, I want to simply replace "Harry" with "Tom" by somehow referring to the selected name in a drop-down list cell.
My hope is that by doing it this way, I will not have to bring down the "Replace" dialog box, highlight the cells containing the formulas, and avoid the aggravating possibility of misspelling the name in the Replace box and enduring a step by step "cancel" for every cell highlighted.
In spite of all the variations in cell/formula coding, such as simply putting the drop-down cell location in place of the worksheet name, that I can think of the results are failures.
Hoping to get an answer that doesn't require VBA. However, if I have to do that, I guess I'll have to learn it.

VoG · Feb 12, 2011

With your validation in A1 try

=INDIRECT("'"&A1&"'!C9")

grandpa_exceler · Feb 13, 2011

(EXCEL 2007, Windows Vista 32 bit)
Thanks, Peter, for your reply. I tried your solution on a simple formula and it works like gangbusters!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

> </o

>
Here’s the thing--- I posted the simple formula and crossed my fingers that it would work on the more complex formula I’m actually wrestling with.
<o

> </o

>
I’ve built a workbook with several worksheet tabs identifying “customers”, like Harry, Jim, etc. In those sheets is a table 24 rows deep and 30 columns wide. In one of the columns I can enter various “salespeople”, like Tom, Joe, etc. In the other columns I enter related data. I’ve added another worksheet (the Data-Gathering worksheet) containing a table to use a formula by which you can select a Customer from a drop-down box, then select a particular sales person from another drop-down box. All data in each row and column corresponding to that sales person in the Customer’s worksheet is to populate a related row in the Data-Gathering Worksheet. All other salespersons and their data are ignored.
<o

> </o

>
Suppose that the column containing the sellers in Customer Harry’s worksheet reside in rows in range (A1:A10) and the corresponding data to be gathered resides in Customer Harry’s worksheet column (B1:B10) column in the Data-Gathering Worksheet populates with Customer worksheet cell range A1:A10; that the customer’s list is in Data-Gathering worksheet’s drop-down box, cell D8; and that the salesperson’s list is in Data-Gathering worksheet’s drop-down box, cell D7. The conditional formula I’ve constructed in the Data-Gathering worksheet’s first data row, without implementing drop-down boxes, would look like:
<o

> </o

>
=IF(‘Harry’!A1="Tom",’Harry'!B1,“ “). The formula in the next row would be:
=IF(‘Harry’!A2="Tom",’Harry'!B2,” “),
Etc.
This works great.
<o

> </o

>
So, by implementing drop-down boxes I would like to substitute the selected name in place of the worksheet tab, like instead of Harry, drop-down box cell D8; and substitute the selected name in the seller’s drop-down box cell D7:
<o

> </o

>
=IF((‘$D$8’!A1)=$D$7,’$D$8'!B1,“ “),
=IF((‘$D$8’!A2)=$D$7,’$D$8'!B2,” “).
<o

> </o

>
Of course, if you do this, the formula doesn’t work. You get a message saying, “The formula you typed contains an error.” I’ve tried minor variations of this to no avail.
<o

> </o

>
When I substitute your proposed solution into the formula the cell responds with #REF!. In addition, “Filling Down” repeats the exact formula rather than stepping to the next row.
<o

> </o

>
My goal is to create a “protected” Data-Gathering worksheet, in which the correct drop-down style formula will work and the Data-Gathering worksheet’s columns can be populated by using this formula (adjusted for corresponding data columns in the Seller’s worksheet) in the first row, and filled down the Data Gathering Worksheet’s columns’ range using the “Fill > Down” function.
<o

> </o

>
Can it be done? Or should I try a completely different tack?

VoG · Feb 14, 2011

Try like this

=IF(INDIRECT("'"&D7&"'!A1")=INDIRECT("D8"),INDIRECT("'"&D7&"'!B1"),"")

grandpa_exceler · Feb 14, 2011

Hi, Peter,
I tried your second solution and it didn’t work, so I started substituting parts of your solution with the related parts of my original formula. When I discovered I had miss-typed by placing the “ “ in the middle of the “IF” equation instead of at the end of the equation where it should have been all along, and substituted the actual drop-down cell reference into the middle instead of the INDIRECT(cell reference), it worked!
So, to recap, if I had correctly used your original solution, mission would have been accomplished.

Here’s the final equation:

=IF(INDIRECT("'"&$D$7&"'!$AO9")=$D$8,INDIRECT("'"&$D$7&"'!$C9")," ")
Where $AO9 = the customer’s worksheet cell location for the sales person's name, and $C9 = the related sales person’s data that I want to capture in my table.

Two unexpected things happened, but these I can deal with:
--- The fonts in the formula cells reverted back to EXCEL’s default fonts,
--- The Conditional formatting of coloring alternate rows disappeared, with these cells going to No Color. No problem. I just went in and restored the desired formatting. I won’t be able to just “fill down” the formula because the customer cells $AO9 and $C9 in the INDIRECT portions do not advance to the next row, so I’ll just manually enter the changes.

I also checked the table in “protected” mode, and it still works fine.

[FONT=&quot]I want to tell you how much I appreciate your help, your patience, and your prompt response. You’ve made my day!

[/FONT]

Using Drop-Down Selections To Replace Worksheet Names in Formulas

grandpa_exceler

New Member

Excel Facts

VoG

Legend

grandpa_exceler

New Member

VoG

Legend

grandpa_exceler

New Member

Similar threads

Forum statistics

Share this page

Using Drop-Down Selections To Replace Worksheet Names in Formulas

grandpa_exceler

New Member

Excel Facts

VoG

Legend

grandpa_exceler

New Member

VoG

Legend

grandpa_exceler

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock