Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on Another Workshe
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on AnotherWorkshe
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on Another Wor
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. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on Another Workshe
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on Another Wor
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! . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation List from a Dynamic Named Range on Another Wor
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation via Dependent List defined by Dynamic Range | Excel Discussion (Misc queries) | |||
Changing named Validation list to Dynamic list. | Excel Discussion (Misc queries) | |||
Data validation drop downs don't recognize dynamic named range | Excel Discussion (Misc queries) | |||
Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |