
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
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.
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))
to=INDEX(LF_MAT,,MATCH(material,LF_DROPDOWN,0))
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
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.
ASKER
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.