Data Validation list based on cell input

[Jeffrey Mahoney]

I want to create a formula inside a data validation to provide a list of values based on the input in a cell. I have a table of hazards (below named "Hazards2_tbl"). When the user selects H2S I want the data validation to lookup the named range from the hazard table, which in this case is "Chem1_list" and show the list of entries from the Chem1_list named range.

Unknown
	C	D	E
3	Abbreviation	Full Name	Magnitude 1 List
4	#2 Deprop CHG	#2 Depropanizer Charge	Chem1_list
5	#2 Deprop FD	#2 Depropanizer Feed	Chem1_list
6	#2 Deprop RFLX	#2 Depropanizer Reflux	Chem1_list
7	H2S	Acid Gas, Hydrogen Sulfide Rich Gas	Chem1_list
8	ASO	Acid Soluable Oil	Chem1_list
9	Alkylate	Alkylate	Chem1_list
10	Alky Debut FD	Alkylation Debutanizer Feed	Chem1_list
11	Alky Debut O/H	Alkylation Debutanizer Overhead	Chem1_list
12	Alky Debut RFLX	Alkylation Debutanizer Reflux	Chem1_list
13	Alky Deprop FD	Alkylation Depropanizer Feed	Chem1_list
14	Alky Deprop O/H	Alkylation Depropanizer Overhead	Chem1_list
15	Alky Deprop RFLX	Alkylation Depropanizer Reflux	Chem1_list
16	NH3	Ammonia	Chem1_list
17	Sour Gas	Ammonia/Hydrogen Sulfide Rich Gas	Chem1_list
18	Antifoam	Antifoam	Chem1_list
19	BFW	Boiler Feed Water	Steam1_list
Lists

I've tried using =INDIRECT(VLOOKUP(F12,Hazards2_tbl,3,FALSE)); where F12 contains H2S

 

[Leith Ross]

Hello Jeffery,

Add this Data Validation (List) formula to cell $A$4 and drag it down.

=INDIRECT(OFFSET($A4,0,2,1,1))

 

[Jeffrey Mahoney]

So, I think I didn't fully explain my need...

On one sheet I have users filling out a form. One of the fields on that form is a hazard that they choose from a list (from the abbreviation column C above). The next two fields need to be dynamic data validation lists based on the choice in the hazard field called Mag1 and Mag2. The list chosen for Mag1 is found by name on the hazard table column E (Magnitude 1 List).

So.. if the user chooses H2S in the hazard field, Mag1 data validation should return the list of values in the named range Chem1_list. If the user chooses BFW in the hazard field, Mag1 data validation should return the list of values in the named range Steam1_list.

I was hoping that I could use a formula in the Mag1 data validation that would dynamically choose which list to return.

I was playing around with some options and I found out something about dynamic named ranges. I could be wrong, but it looks like INDIRECT will NOT work with dynamic names ranges. I will work with hard coded named ranges.

The formula I used before: =INDIRECT(VLOOKUP(F12,Hazards2_tbl,3,FALSE)) will work to return a range only if the range is not dynamic.

Does anybody else have a solution?

Jeff