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

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")

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 

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?