Forum Discussion

vitoaiaco's avatar
vitoaiaco
Brass Contributor
Feb 05, 2024

Problem with Data Validation Dependant Dropdown

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

  • 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.

     

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

     

    • vitoaiaco's avatar
      vitoaiaco
      Brass Contributor

      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

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        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.

         

         

Resources