Data Validation - Range Problem

[Dave Eade]

Git a problem with Data Validation.
In column A rows 1-10 hold values, then for row 11 I've set the DV to "look
up" the range 1-10 for values to use - so far so good.

When I copy A1 and paste downwards for the next 100 rows, the DV "adds" 1 on
to the range ie for row 12 the range is specified as 1-11, for 13 1-12 and 14
1-13 etc.

I've tried a straighforward paste, as well as a copy and format paste but it
doesn't change.

Other columns I've done this with are fine - I just can't see what the
problem is with this column - I could rewrite it all out but I'd prefer to
know whats going wrong...

Thanks

 

[Eduardo]

Hi Dave,
If you want to get always the range lets say A1:A11 when copying it you have
to use
$A$1:$A$11
If this was helpful please say yes

 

[Dave Eade]

No it doesn'e work.

If I have $A$1:$A$11 in A12 and copy to A20, when I open the Data Validation
Window it has changed the range to $A$1:$A$19

 

[Eduardo]

Hi Dave,
It works for me please be sure that you choose custom and then enter the
formula as well check that you have the $ before and after the A

 

[Dave Eade]

No, and it's only in this one column - when I use data validation in other
columns it's fine.
It's as if there is a formula embedded in the data validation rules that
always adds 1 onto the last cell reference when pasted.