Auto populate a cell with a table (column) on another sheet.

[Steven2285]

I have an application where I want to auto populate a cell with a drop down from a (column) table when a certain value is chosen in a third cell. This is so the User can only make a selection from the options that are limited to the specific Manufacturer they choose. Any thoughts?

[#2 · Accepted solution]

@Steven2285 

See Create Dependent Drop-Down Lists 

[#3]

Thank you very much. That is exactly what I needed! @HansVogelaar 

[#4]

So, that helped to get me started, but I'm still having trouble. I'll try to explain better. Cell C19 is auto-filled with information from Cell C8 which comes from a drop-down with info from another sheet on it. I want Cell E19 to be a dependent drop-down that returns a column from another sheet that changes with every different selection in C8. For example, when C19='SHEET2'AH2 then the drop-down will show only info from SHEET2 AH18:AH81. Please help. Thank you.

[#5]

@Steven2285 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#6]

Here you are, sir. So you are aware, SHEET2 is actually the sheet labeled DO NOT DELETE. @HansVogelaar 

[#7]

@Steven2285 

See the attached version. I created several defined names to make it work - see Formulas > Name Manager.

Note: there doesn't appear to be a range for FBS, so you won't see a drop down list in C19 if you select FBS in C8.

[#8]

Looks great, thank you! Will you send me the formula you created, please? It isn't showing up in the cell. It is E19 that has to have the drop-down in it. Thanks again, you've helped me with a massive headache! :-[]

[#9]

@Steven2285 

There is no formula in C19.

Select that cell and then click Data Validation on the Data tab of the ribbon.

You'll see that the Source of the data validation list is a formula.

[#10]

Sorry. I realized my foolish mistake right after I sent my response. Thank you for your patience and help.

[#11]

Good morning. I want to do the same thing you helped with yesterday with other options on my worksheet. While I have been able to create names for the Tables I want to use, I'm having trouble deciphering the process and how to use a similar formula in other areas. Any insight you can offer would be greatly appreciated.

[#12]

@Steven2285 

Let's look at an example.

One of the manufacturers is Medallion Gold. The list of corresponding items is in the Medallian Gold column of the table named Table35 on the DO NOT DELETE sheet.

I selected that column (not including the column header), then clicked in the name box on the left hand side of the formula bar and typed typed the name Medallion_Gold and pressed Enter. Note that I replaced the space with an underscore; this is necessary because defined named cannot contain spaces.

The defined name now appears in Name Manager (available on the Formulas tab of the ribbon):

I similarly created names for each of the manufacturers (except for FBS since I couldn't find the corresponding items), if they didn't exist already. The names must correspond exactly to the names of the cabinet manufacturers, with spaces replaced with underscores.

Next, I created a data validation rule for C19 on the Phase 1 sheet:

The source of the drop down list is =INDIRECT(SUBSTITUTE(C8, " ", "_"))

This takes the name of the selected manufacturer, and replaces any spaces (if present) to underscores.

INDIRECT then converts this name to a reference to the corresponding named range.

So if you select Matsercraft in C8, the list in C19 is based on the range named Mastercraft.

And if you select Medallion Gold, the list is based on the range named Medallion_Gold.

Does that help?

[#13]

Yes it does help. Thank you. My worksheet has other options for the varying Manufacturers that are included in other ares. (Drawer Box Type, Rollout Type, Drawer Glide, Hinge Type.) I have created and will create lists on the 'DO NOT DELETE' sheet with the different options for each Manufacturer. I would like to auto populate those cells with a drop down from the options on the sheet. Is that possible? If so, how do I do it? I have included a copy of the Worksheet again so you can see what I am referring to. Thanks again! @HansVogelaar 

[#14]

@Steven2285 

You'd have to create names such as Medallion_Silver_Drawers and Medallion_Gold_Drawers for this to work.

The data validation of C28 could then have as source

=INDIRECT(SUBSTITUTE(C8, " ", "_")&"_Drawers")

[#15]

Thank you so much! I wish I could buy you a steak dinner!