Forum Discussion
Problem with Data Validation Dependant Dropdown
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.
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 365vitoaiaco 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
