Forum Discussion

Jambo97's avatar
Jambo97
Copper Contributor
Nov 01, 2024

Dependent Drop Downs across multiple rows in a spreadsheet

Hello. I've been trying to create a spreadsheet that has a dependent dropdown column. However, all the guides and advice I've seen so far have been functional only when you only need a single cell or...
excel
Formulas and Functions
office 365
HansVogelaar's avatar
HansVogelaar
MVP
Nov 01, 2024

Jambo97 

If you select, for example, B2:B1000 and create data validation of type List with source =INDIRECT(A2), Excel will automatically adjust this: in B3 it will use =INDIRECT(B3), in B4 it will use =INDIRECT(B4), etc.

Jambo97's avatar
Jambo97
Copper Contributor
Nov 01, 2024
It won't allow me to enter =INDIRECT(A2) I get the error message "This entry leads to an error. Try enterting different values to continue."
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 01, 2024

    Jambo97 

    If you can't get it to work: Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • Jambo97's avatar
      Jambo97
      Copper Contributor
      Nov 02, 2024

      Sure, I can send a link because its in Office 365. It's just a silly project so no sensitive data either.

      https://1drv.ms/x/c/065d04dd89e46872/EWEH9GHaAOxDn1IGlftc4SEB83Mz-7_0XJibHXLogNtOrw?e=ync9Pd

      I have the "Class" column, which is working fine. But "Subclass", drawing from the table in the Values sheet, just doesn't do what I want it to do. It's only reading the first rows entry in the "Class" column.

       

      If it's the case that I have to do something more complicated than "INDIRECT" I'll just keep looking for other solutions.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 01, 2024

    Jambo97 

    That means that A2 currently doesn't evaluate to a valid range address or named range...

Resources