SOLVED

Problem with Data Validation Dependant Dropdown

Copper Contributor

Hi everyone!

Looking for your help!

I have attached the copy of a file I'm working. In the list tab, you'll find a list of different vehicles split by categories (MTL, CAT, LDV, BUS). I would like to create a dependant drop down menu in the RAW DATA tab with Fleet numbers depending on the 1st category selection. The data validation works perfectly when selecting MTL or CAT, but for LDV and BUS only gives me the first option on the list.

 

Your help with fixing this would be hugely appreciated.

 

Many Thanks

Vito

4 Replies

@vitoaiaco The last part of the DV formula looks like this:

MATCH($B2106,LISTS!$B$1:$C$1,0)-1,98)),1)

 

change that to:

MATCH($B2106,LISTS!$B$1:$E$1,0)-1,150)),1)

 

The reason for changing 98 to 150 is needed because the list for MTL will not be complete as it has more than 98 elements.

 

Having said that, if you are on a modern Excel version there are easier ways to do this. But, for now, I just focused on resolving the issue with the current approach.

 

Thank you Riny...
Even when changing the range as you mentioned, I'm still getting only 1 option in the dropdown list when selecting LDV or BUS.

If there's an easier way, I would appreciate your advise... I'm on Office 365

best response confirmed by vitoaiaco (Copper Contributor)
Solution

@vitoaiaco Aha! I missed that you also had a reference to C1 in the beginning of the formula. change that also to E1 and it will work.

 

Ignore what I said about an easier way. It's more a different way as I don't particularly like the use of OFFSET. Reduced your file to only 4 rows of raw data to test it. See attached.

 

 

Thank you very much!!!

Problem solved
1 best response

Accepted Solutions
best response confirmed by vitoaiaco (Copper Contributor)
Solution

@vitoaiaco Aha! I missed that you also had a reference to C1 in the beginning of the formula. change that also to E1 and it will work.

 

Ignore what I said about an easier way. It's more a different way as I don't particularly like the use of OFFSET. Reduced your file to only 4 rows of raw data to test it. See attached.

 

 

View solution in original post