Hi:

I have an unusual spreadsheet issue.

I have a document that will eventually be 50 pages long.

I have 10 cells - A1:A10. I want the validation list to be limited to
whatever is in col. B1-B10. Therefore, the user would be able to select from
any of those 10 in col. B in the A drop-down. So, I use the formula:
=$B$1:$B$10. seems simple enough right?

However, I want to continue this down 10 rows for ten drop-downs validation
for all 50 pages. Problem is, if I keep the formula the way it is and
copy/drag it, it just refers the lower rows up to the higher rows in col. B.
which of course I don't want. If I try removing the dollar signs on the row
numbers, then it treats each individual cell differently. Therefore, A11
validation looks like: =$B11:$B20 BUT...A12 looks like: =$B12:$B21 which I
don't want it to. I want that next set of 10 rows to have the same 10
drop-downs.

Sorry this is so complicated! The problem then is obviously I can't manually
create each and every reference- my hand would fall off!! :) Naming the cells
would be just as complicted, time consuming and high chance of error.

Thanks!


Please help. thank you!