Data Validation drop down list, Online Excel - help

[Davit2005]

Hi All,

So a while since I have really done this sort of thing but managed to do it in previous versions, may be I'm jus having a day, lol.

 

I have a spreadsheet with multiple rows and I'm trying to create a validation in a couple of columns that I can have a drop down list in the cells. When I select the cells I want the validation to be applied to it sort of misses list items like it is skipping a list item for each cell consecutively to the stage where nothing is shown in the drop down list at all.

 

I've tried selecting all the rows I want the validation in and copying and pasting the formula, what am I doing wrong.

 

The validation is:

Allow: List

Source: =Lists!A2:A40

Ignore Blank: Ticked

In-Cell Drop Down: Ticked

[Steve21]

If I think what you mean is what you mean, as you copy it down it'll change the values of A2:A40 unless you're using $'s to lock it

 

 

e.g. Using =$N$6:$N$10 instead of =N6:N10 (As when you copy that down it turns into =N7:N11 and will miss the top item 2nd time it'll be =N8:N12 missing 2 items etc)

 

Steve

Edited July 3, 2024 by Steve21

[Davit2005]

If I think what you mean is what you mean, as you copy it down it'll change the values of A2:A40 unless you're using $'s to lock it

 

 

e.g. Using =$N$6:$N$10 instead of =N6:N10 (As when you copy that down it turns into =N7:N11 and will miss the top item 2nd time it'll be =N8:N12 missing 2 items etc)

 

Steve

You are a star. The old dollar trick, lol.

[andy_b]

I tend to use named ranges for my validation lists to avoid this problem.

[Davit2005]

I tend to use named ranges for my validation lists to avoid this problem.

 

Thanks, Will have a look into that :-)

 

Excel is a bit annoying, is it jus the online version or installed version. Adding $ kept on adding the range so I had to compose the formula then paste it in, lol