Data Validation List from a Dynamic Named Range on Another Workshe

[Jeremy]

Hello all. I have searched all over the web for an answer to this question
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]

I hope this helps. I'm not following the formulas. I haven't used them
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]

Ok Ziggy... well you formula didn't work with my VBA, but it got me thinking
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!

 

[Roger Govier]

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
http://www.contextures.com/xlDataVal15.html

 

[teylyn]

Roger's INDEX method of creating dynamic ranges may take a while t
sink in but once you'v grasped it, you'll love it! No more volatil
OFFSET!

cheers, teylyn

 

[Jeremy]

Hey Roger. Thanks for the input. I tried your method and my sorting will no
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]

Hi Jeremy

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 name
on a different sheet, but it may work? If you can get it to work let m
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 become
simply:

=_DVList

As G14 is altered so the dependent list will update in tur
irrespective of whether the Names in use are dynamic.