data validation problem

[carmel]

I'm using data validation to create a user form for my
colleagues. I want to copy the specific data validation
settings from the first cell to the next 200 cells down,
in order to avoid having to re-enter the conditions
manually. When I do copy and paste special, Excel moves
the source down one cell each line so my list becomes less
and less full. Specifically, my source for the first cell
(A1) is =AA1:AA100. I want to copy this into cells
A1:A100, but the source in cell A2 changes and becomes
=AA2:AA101, then in A3 it becomes =AA3:AA102. I changed
the "extend list formats and formulas" to off, but that
doesn't help.
Please does anyone have any ideas?

 

[Don Cameron]

Carmel
In cell A1 for the formula use absolute reference $AA$1:$AA$100
When you copy down the formula will remain exactly the same in every cell.
BTW you can point to the bottom right of the cell and when you get a "+"
sign just drag down - no need to paste special.
Cheers
Don

 

[carmel]

Thanks Don!

-----Original Message-----
Carmel
In cell A1 for the formula use absolute reference $AA$1:$AA$100
When you copy down the formula will remain exactly the same in every cell.
BTW you can point to the bottom right of the cell and when you get a "+"
sign just drag down - no need to paste special.
Cheers
Don





.