Populate dropdown list of values from table based on cell value

[dbro34]

Hello,
I've searched this forum and many examples on google of a formula or data validation list formula that might accomplish my needs to no avail. I'm looking to populate a dropdown box's list of values from a table on another sheet that matches a cell value. I've seen many examples of the "cascade" dropdown using the indirect, but don't believe it applies to my situation.

Here is a barebones limited version of the sales order template [Sheet1]. The objective would be to select the options (Choice) for each line item into column B. The values would come from the data table on Sheet2.

Sales Order Template.xlsx
	A	B
1	General
2	Item	Choice
3	NE State
4	Décor Kit
5	Countertop Color
6
7	Electrical
8	Item	Choice
9	Panel Box
10	Minisplit
Sales Order Template

Here is the table that holds the choices for the items [Sheet2]. Column C has the line item based on Sheet1 and Column D has the values I want to show in the dropdown on Sheet1, Column B. I would like a formula for the cell or the data validation that gets me to the solution. In the real world, the sales order template would have many more sections and line items and the table of choices would be much larger, so the dropdown list of values would need to be dynamic as choices are added/removed.

Sales Order Template.xlsx
	A	B	C	D
1	Category	Department	Item	Choice
2	GENERAL	GENERAL	NE State	Y
3	GENERAL	GENERAL	NE State	N
4	GENERAL	FINAL	Décor Kit	Coral
5	GENERAL	FINAL	Décor Kit	Sage
6	GENERAL	FINAL	Countertop Color	White
7	GENERAL	FINAL	Countertop Color	Grey
8	ELECTRICAL	ELECTRICAL	Panel Box	50 Amp
9	ELECTRICAL	ELECTRICAL	Panel Box	100 Amp
10	ELECTRICAL	ELECTRICAL	Minisplit	Y
11	ELECTRICAL	ELECTRICAL	Minisplit	N
Choices

Either I can't wrap my head around how to link up the lists or delirium is setting in
Any help is greatly appreciated and thanks in advance!

 

[duggie33]

Hi *dbro34,

It can be done, but I need help remembering the exact steps. I would try to follow the example on ExcelJet.net, a great reference website. How to make dependent dropdown lists in Excel

I hope that helps,

Doug

 

[dbro34]

Hi *dbro34,

It can be done, but I need help remembering the exact steps. I would try to follow the example on ExcelJet.net, a great reference website. How to make dependent dropdown lists in Excel

I hope that helps,

Doug

Hi duggie,
This is what I was referring to as the cascade with indirect setup won't really work in my scenario. The Indirect uses the vertical list to populate the list of values for each category. I'd prefer not to structure the data in this way as it would be a pain to scroll through the options in this manner.

I will try an example setup and see how this might work and report back. Thanks for the response.

 

[dbro34]

Hi *dbro34,

It can be done, but I need help remembering the exact steps. I would try to follow the example on ExcelJet.net, a great reference website. How to make dependent dropdown lists in Excel

I hope that helps,

Doug

Looks like the data table has to be on the same sheet for this example to work, which is not ideal. So back to my original question...

It's almost like I need to populate the dropdown in B with a lookup formula that returns all values (Choices) for any matching Item in the data table.

Any other thoughts?

 

[duggie33]

The FILTER function would be ideal to return all Choices for any Item but it cannot be used within Data Validation. I searched around for a while and didn't find an obvious way to do it.

I would create another sheet that dynamically creates the lists of Choices for each Item. You could use a TRANSPOSE/SORT/UNIQUE of the Items column to get all of the Items for headers. Under each Item, you could use the FILTER function to return the Choices for that Item. The range sizes should be set up so they increase automatically as you add more Items and Choices. Named ranges, OFFSET function, etc.

With that, you could then set up the data validation with INDIRECT or however the examples you have found.

I hope that helps,

Doug