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!

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.

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!

"Ziggy" wrote:

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

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

--
Regards
Roger Govier

Jeremy wrote:
> 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!

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)
> 
> --
> Regards
> Roger Govier
> 
> Jeremy wrote:
> > 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!


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

http://www.thecodecage.com/forumz

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" 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
> http://www.contextures.com/xlDataVal15.html
> 
> --
> Regards
> Roger Govier
> 
> Jeremy wrote:
> > 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!
> .
> 

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
--
Regards
Roger Govier

Jeremy wrote:
> 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" 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
>> http://www.contextures.com/xlDataVal15.html
>>
>> --
>> Regards
>> Roger Govier
>>
>> Jeremy wrote:
>>> 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!
>> .
>>

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.





Roger Govier;684607 Wrote: 
> 
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
> --
> Regards
> Roger Govier
> 
> Jeremy wrote:
> > 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" 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)
> >>
> >> --
> >> Regards
> >> Roger Govier
> >>
> >> Jeremy wrote:
> >>> 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!
> >> .
> >>


-- 
Dulanic
------------------------------------------------------------------------
Dulanic's Profile: 1734
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189913

http://www.thecodecage.com/forumz