Link to home
Avatar of Ted Brister
Ted Brister

asked on

Single Table Multi Column Validation List Not Working

I'm trying, unsuccessfully, to create 2 validation lists based on a table.  I would like this to work on a row by row basis.  The validation list based on the table headers works fine, the secondary validation doesn't work at all.

Goal: To create a dropdown in the material column that will only display the material type on row 2 of the table based on a match.  

Looking @ the example, row two Material (NTX Disposal Log!D2) would display a validation list based on the entry in the Disposal Site Column (NTX Disposal Log!B2) by matching the the entry to a table header in the LF_MAT table and returning the values below the matched value, in this case, City of Denton, which would return a validation list selection of MSW.

I was trying to follow the instructions laid out here:
https://www.excel-university.com/use-the-column-header-to-retrieve-values-from-an-excel-table/

What I don't understand is how to adapt

=INDEX(tbl_choices,,MATCH(region,dd_regions,0))

Open in new window

to
=INDEX(LF_MAT,,MATCH(material,LF_DROPDOWN,0))

Open in new window


Table Name: LM_MAT (tbl_choices in the web instructions)
Named Range: LF_DROPDOWN (=LF_MAT[#Headers]) (dd_regions in the web instructions)
Name Rage: MAT_DROPDOWN (=INDEX(LF_MAT,,MATCH(MATERIAL,LF_DROPDOWN,0)) (dd_reps in the web instruction
Disposal Site Validation: =LF_DROPDOWN (works fine)
Material Validation: =MAT_DROPDOWN (doesn't work @ all)

NTX Disposal Log-Combined-Running.xlsx

Any help would be greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ted Brister
Ted Brister

ASKER

Mr. Hensen,
First, thanks for your response.  

I'll update the INDEX formula & remove the extra comma.  If I define MATERIAL as a name, how would I define the range?  In the post I was using as an example, they never defined REGIONS, which is equivalent to my MATERIAL.