troubleshooting Question

Single Table Multi Column Validation List Not Working

Avatar of Ted Brister
Ted Brister asked on
* Excel TableMicrosoft Excel* Data ValidationMicrosoft Office
2 Comments1 Solution19 ViewsLast Modified:
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
Rob Henson
Finance Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004