Data validation- absolute ref or not- neither works

[Roady]

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!

 

[jlclyde]

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

 

[Roady]

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

 

[Jim Rech]

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
| 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:
|
| > > 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
| >