Forum Discussion
Auto populate a cell with a table (column) on another sheet.
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.
- Thank you so much! I wish I could buy you a steak dinner!
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")
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
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?
- 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.
- Sorry. I realized my foolish mistake right after I sent my response. Thank you for your patience and help.
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.