Data Validation List from a Dynamic Named Range on Another Workshe
Jeremy
and I've come to the conclusion that there is either a problem with the way
I've built my dynamic named range or I'm a complete moron.
I have two worksheets. The first is where the data will be used and viewed,
the second is where the lists reside that the data validation draws from. I
have no problem getting the first level of data validation to work (ie,
select the cell, Data Validation, Allow List, Source =OFFSET(Parks,1,0)
because I have a header). The problem occurs when I try to validate another
cell based on the first cells validation answer.
The formula I'm using on the second worksheet, called "Lists" is...
=OFFSET(Lists!$C$1,0,0,MATCH("*",Lists!$C:$C,-1),1)
Obviously this is in column C and I want it to expand as values are added to
it. Now I need to mention that I also have some VBA associated with each list
that automatically alphabetizes the range when a new value is added. The VBA
code is...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("Parks").Sort key1:=Range("Parks"), Order1:=xlAscending, _
Header:=xlYes, MatchCase:=False
End Sub
What I need to accomplish, is once I select a value from the first drop
down, I need the values offered in another drop down (in the same row but a
different column) to update based on the first cell. I know INDIRECT won't
work and I've tried OFFSET but can't get any of them to give me a drop down
list. I will also be adding more VBA to the first sheet (without the lists)
that lets me populate the cell with dependent data validation with multiple
values per cell...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Cells = Range("D6") Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Now this is the very first time I have ever "coded" VBA (actually borrowed
and modified code) so be gentle. Any help or advice will be greatly
appreciated. Thanks!
Ziggy
with that syntax.
Here is what I use to name a range that will vary in length.
=Offset('SheetName'!$A$1,1,0,CountA('SheetName'!$A:$A)-1)
It needs to be used in a column where that is filled with data right
to the end of the data fields.
Jeremy
and I changed my dynamic range name formula to...
=OFFSET(Lists!$C$2,0,0,MATCH("*",Lists!$C:$C,-1)-1)
I'm not sure what this bought me, but it seems to work better and eliminates
the one blank line in the drop down list. I'll try some of the various data
validation list formulas tomorrow and see if I have any better luck. I've
been at work for 14hrs. today and my brain is toast.
Thanks for your input!
"Ziggy" wrote:
> .
>
Roger Govier
Take a look at a tutorial I wrote on using INDEX rather than OFFSET as a
means of creating dynamic named ranges in situations where you want to
used dependent drop downs lists.
you will find a copy here
http://www.contextures.com/xlDataVal15.html
--
Regards
Roger Govier
teylyn
Roger's INDEX method of creating dynamic ranges may take a while to
sink in but once you'v grasped it, you'll love it! No more volatile
OFFSET!
cheers, teylyn
Roger Govier;680193 Wrote:
>
Hi Jeremy
>
> Take a look at a tutorial I wrote on using INDEX rather than OFFSET as
a
> means of creating dynamic named ranges in situations where you want to
> used dependent drop downs lists.
>
> you will find a copy here
> 'Excel Data Validation -- Dependent Lists With INDEX'
(http://www.contextures.com/xlDataVal15.html)
--
teylyn
Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189913
Jeremy
longer work and I can't just add a value to any cell in the column and have
it alphabetize it. I will continue experimenting though.
"Roger Govier" wrote:
> .
>
Roger Govier
I can't understand why your sorting won't work as a result of my DV method.
If you want to send me a copy of the workbook, with a full explanation
of the problem, I will take a look.
#Send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
Dulanic
Maybe you can get this to work, I have had trouble using it with names
on a different sheet, but it may work? If you can get it to work let me
know for sure!
This comes from a user on another forums:
We can simply create one further Define Name:
Name: =_DVList
RefersTo: =Evaluate(INDIRECT("G14"))
At which point the source for our dependent Validation List becomes
simply:
=_DVList
As G14 is altered so the dependent list will update in turn
irrespective of whether the Names in use are dynamic.
> >> 'Excel Data Validation -- Dependent Lists With INDEX'
(http://www.contextures.com/xlDataVal15.html)
> >>
--
Dulanic
------------------------------------------------------------------------
Dulanic's Profile: 1734