Data validation- absolute ref or not- neither works
 

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!

Data validation- absolute ref or not- neither works
 

On Sep 29, 1:40*pm, Roady wrote:
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!

You coudl put int he first one as =$B$11:$B11 and then drag it down.
This would be much less manually changing.
Or you coudl go with named ranges. Go to insert/Name name a range and
then in validation just call that name as the list.

Thanks,
Jay

Data validation- absolute ref or not- neither works
 

Thanks for the response but that does not address what I outlined that I need
to accomplish.

Does anyone have another suggestion? Please read my post. Thank you! I much
appreciate it!

JR

"jlclyde" wrote:

On Sep 29, 1:40 pm, Roady wrote:
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!

You coudl put int he first one as =$B$11:$B11 and then drag it down.
This would be much less manually changing.
Or you coudl go with named ranges. Go to insert/Name name a range and
then in validation just call that name as the list.

Thanks,
Jay

Data validation- absolute ref or not- neither works
 

You need a macro:

Sub a()
Dim Counter As Integer
For Counter = 1 To 50
With Cells((Counter - 1) * 10 + 1, 1).Resize(10)
.Validation.Delete
.Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Formula1:="=" & .Offset(0, 1).Address
End With
Next
End Sub


--
Jim
"Roady" wrote in message
...
| Thanks for the response but that does not address what I outlined that I
need
| to accomplish.
|
| Does anyone have another suggestion? Please read my post. Thank you! I
much
| appreciate it!
|
| JR
|
| "jlclyde" wrote:
|
| On Sep 29, 1:40 pm, Roady wrote:
| 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!
|
| You coudl put int he first one as =$B$11:$B11 and then drag it down.
| This would be much less manually changing.
| Or you coudl go with named ranges. Go to insert/Name name a range and
| then in validation just call that name as the list.
|
| Thanks,
| Jay
|