Forum Discussion

Howard_Jones's avatar
Howard_Jones
Copper Contributor
Nov 07, 2019

Drop down list data validation

Thanks for your assistance. I have created some new worksheets with the drop down list target choices and set up the drop down lists on the main worksheet. However, my drop down list choices are decrementing with each line I go down on the main worksheet.

 

If, say, I have 20 choices in one of my drop down worksheets, by the time I get to a cell in the column on the main worksheet containing the link to that list, if I'm on line 21, there are no options displayed in the drop down for that cell. When I take a look at the data validation, I can see that the main worksheet is not referring to the 20 drop down list options as a set feature, but moves down a line in the target worksheet each time I go down a line on the main worksheet.

 

I hope I have explained that clearly. I seem to missing making some correct setting in my data validation, but I can't pick what it is that I'm overlooking.

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Howard_Jones 

    Suppose your list is in A1:A20. Make sure that the reference to this list in the Data validation is like

     

    =$A$1:$A$20

     

    Leaving the dollar signs out will cause the effect that you describe.

    • Howard_Jones's avatar
      Howard_Jones
      Copper Contributor

      Hi Riny, Thank you. You have more $ signs in your string than were shown in the MS help section about this. I will try this out in the morning.

       

      Regards,

      Howard

Resources