Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
All I have researched says I am doing it correctly, but cannot make a dynamic named range work if it is on another sheet.
All source lists and named ranges are on sheet1 and the dependent drop downs are on Dep. Drop Down sheet. A2 drop down works fine with Blist, Clist, Dlist. They use non-Offset refers to formulas. Alist uses this formula in the source box OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$200), 1) B2 drop down source box uses =INDIRECT($A$2) It's probably right at my feet, but can't figure??? Here is a link. https://www.dropbox.com/s/4pyllrq4qo...rop%20Box.xlsm Thanks, Howard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
I found the solution I needed, using Tables on the source sheet instead of an OFFSET formula.
Howard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Howard,
Am Wed, 21 May 2014 16:53:55 -0700 (PDT) schrieb L. Howard: OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$200), 1) B2 drop down source box uses =INDIRECT($A$2) depending DV only works with fix ranges and not with ranges defined by a formula Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Howard,
Am Thu, 22 May 2014 08:57:25 +0200 schrieb Claus Busch: depending DV only works with fix ranges and not with ranges defined by a formula if you want a dynamic range and a depending DV create the names with VBA: Sub NewNames() Dim myName As String Dim LRow As Long Dim i As Long With Sheets("Sheet1") For i = 1 To 4 myName = .Cells(1, i) LRow = .Cells(Rows.Count, i).End(xlUp).Row ActiveWorkbook.Names.Add Name:=myName, _ RefersTo:=.Range(.Cells(2, i), .Cells(LRow, i)) Next End With End Sub If the ranges caan change use Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target As Range) 'Modify to your Range If Intersect(Target, Range("A1:D20")) Is Nothing Or _ Target.Count 1 Then Exit Sub NewNames End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
On Thursday, May 22, 2014 2:09:47 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 22 May 2014 08:57:25 +0200 schrieb Claus Busch: depending DV only works with fix ranges and not with ranges defined by a formula if you want a dynamic range and a depending DV create the names with VBA: Sub NewNames() Dim myName As String Dim LRow As Long Dim i As Long With Sheets("Sheet1") For i = 1 To 4 myName = .Cells(1, i) LRow = .Cells(Rows.Count, i).End(xlUp).Row ActiveWorkbook.Names.Add Name:=myName, _ RefersTo:=.Range(.Cells(2, i), .Cells(LRow, i)) Next End With End Sub If the ranges caan change use Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target As Range) 'Modify to your Range If Intersect(Target, Range("A1:D20")) Is Nothing Or _ Target.Count 1 Then Exit Sub NewNames End Sub Regards Claus B. Hi Claus, Regular DV's work with the OFFSET formula but NOT dependent DV's? I wrongly have assumed that OFFSET was suitable for all DV's, and this is the first time I tried to set one up for a dependent DV. And it sure enough was not working for me. I'll use you code solution and give it a go. Thanks Claus. Howard |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Howard,
Am Thu, 22 May 2014 04:32:11 -0700 (PDT) schrieb L. Howard: Regular DV's work with the OFFSET formula but NOT dependent DV's? unfortunately! Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
On Thursday, May 22, 2014 4:42:59 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 22 May 2014 04:32:11 -0700 (PDT) schrieb L. Howard: Regular DV's work with the OFFSET formula but NOT dependent DV's? unfortunately! Regards Claus B. Claus, I was successful using a Table for each off sheet dependent list. Where if the table/s is added to or reduced it brings a current list to the DV. Here is a link, I'd be interested in your thoughts on this method. https://www.dropbox.com/s/w5f2q0x5db...rop%20Box.xlsm The DV's in column A & B are the examples. The other DV's on the sheet is where I am in the process of using your code solution to update off sheet lists. I am wondering why the code has a loop that runs 4 times on the named range myName? Howard |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Howard,
Am Thu, 22 May 2014 05:50:07 -0700 (PDT) schrieb L. Howard: I was successful using a Table for each off sheet dependent list. Where if the table/s is added to or reduced it brings a current list to the DV. Here is a link, I'd be interested in your thoughts on this method. if you like Tables then it is the easiest way to have always the correct range. I use it sometimes for Pivot-Tables. The other DV's on the sheet is where I am in the process of using your code solution to update off sheet lists. I am wondering why the code has a loop that runs 4 times on the named range myName? that is for your first example where you needed names from column 1 to 4 (A:D) In this example above you had to change the code to: Sub NewNames() Dim myName As String Dim LRow As Long Dim i As Long With Sheets("Sheet1") ' i is column number (K:M) For i = 11 To 13 myName = .Cells(1, i) LRow = .Cells(Rows.Count, i).End(xlUp).Row ActiveWorkbook.Names.Add Name:=myName, _ RefersTo:=.Range(.Cells(2, i), .Cells(LRow, i)) Next End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
I am wondering why the code has a loop that runs 4 times on the named range myName? that is for your first example where you needed names from column 1 to 4 (A:D) In this example above you had to change the code to: Sub NewNames() Dim myName As String Dim LRow As Long Dim i As Long With Sheets("Sheet1") ' i is column number (K:M) For i = 11 To 13 myName = .Cells(1, i) LRow = .Cells(Rows.Count, i).End(xlUp).Row ActiveWorkbook.Names.Add Name:=myName, _ RefersTo:=.Range(.Cells(2, i), .Cells(LRow, i)) Next End With End Sub Regards Claus B. DING! A bit dense I am. MyName is the name of the list in each column and it get worked on by the code and then moves on to the next list. Thanks Claus. Howard |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
depending DV only works with fix ranges and not with ranges defined
by a formula Not true! My Invoicing: Simple Bookkeeping app uses dynamic ranges on a hidden sheet for its ChartOfAccounts used by the dependant DVs on its 'Income' and 'Expenses' sheets, as well as its 'Summary' (profit/loss) sheet. This was deliberate so any changes in the COA at runtime would automatically reflect in the DVs. Ref to the ranges includes the hidden sheet's name. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Garry,
Am Thu, 22 May 2014 12:24:55 -0400 schrieb GS: Not true! My Invoicing: Simple Bookkeeping app uses dynamic ranges on a hidden sheet for its ChartOfAccounts used by the dependant DVs on its 'Income' and 'Expenses' sheets, as well as its 'Summary' (profit/loss) sheet. This was deliberate so any changes in the COA at runtime would automatically reflect in the DVs. Ref to the ranges includes the hidden sheet's name. I never get a depending DV with INDIRECT to work, if the range is defined by OFFSET formula. From: http://www.contextures.com/xlDataVal02.html: <Using Dynamic Lists Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with dynamic lists.< Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Ref to the ranges includes the hidden sheet's name.
For clarity, see the definition for the dynamic range below. Example from 'Expenses': Expense Category input field DV List ref: =ExpenseCategories Expense Sub-Category input field DV List ref: =INDIRECT(SUBSTITUTE(ExpenseCategory," ","")) ExpenseCategories definition: =OFFSET(Lists!ExpenseCategory_Hdr,1,0,COUNTA(Lists !Expense_Categories)-2,1) ...which is global scope so it can be used by all sheets for an DV. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Garry,
Am Thu, 22 May 2014 12:24:55 -0400 schrieb GS: Not true! My Invoicing: Simple Bookkeeping app uses dynamic ranges on a hidden sheet for its ChartOfAccounts used by the dependant DVs on its 'Income' and 'Expenses' sheets, as well as its 'Summary' (profit/loss) sheet. This was deliberate so any changes in the COA at runtime would automatically reflect in the DVs. Ref to the ranges includes the hidden sheet's name. I never get a depending DV with INDIRECT to work, if the range is defined by OFFSET formula. From: http://www.contextures.com/xlDataVal02.html: <Using Dynamic Lists Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with dynamic lists.< This DV formula... Expense Sub-Category input field DV List ref: =INDIRECT(SUBSTITUTE(ExpenseCategory," ","")) ...uses a local scope defined name (ExpenseCategory) that's column-absolute, row-relative. This is the dependant DV. The controlling DV is the one dependant on the dynamic range named "ExpenseCategories", which is global scope. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Garry,
Am Thu, 22 May 2014 12:51:31 -0400 schrieb GS: This DV formula... Expense Sub-Category input field DV List ref: =INDIRECT(SUBSTITUTE(ExpenseCategory," ","")) ..uses a local scope defined name (ExpenseCategory) that's column-absolute, row-relative. This is the dependant DV. The controlling DV is the one dependant on the dynamic range named "ExpenseCategories", which is global scope. thank you. I will try it. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down with dynamic named range offsheet source list
Hi Garry,
Am Thu, 22 May 2014 12:51:31 -0400 schrieb GS: This DV formula... Expense Sub-Category input field DV List ref: =INDIRECT(SUBSTITUTE(ExpenseCategory," ","")) ..uses a local scope defined name (ExpenseCategory) that's column-absolute, row-relative. This is the dependant DV. The controlling DV is the one dependant on the dynamic range named "ExpenseCategories", which is global scope. thank you. I will try it. Regards Claus B. I should mention that the ExpenseCategories list is vertical as it's part of the ChartOfAccounts list (also vertical). All Sub-Category lists run horizontal off their respective 'parent' in the ChartOfAccounts. Thus the use of SUBSTITUTE() to remove any spaces in the CoA list since these are the defined names of the sub-list dynamic ranges. Also, all ranges named on the 'Lists' sheet are local scope. (Thus the ref in the global RefersTo includes the sheetname!definedname) So... ColA:ColB:ColC:... Chart of Accounts Income Categories:Revenue1:Revenue2:Other Income:Discounts Given Expense Categories CoGS:Inventory:Discounts:Direct Wages:Contracts:Freight In:Freight Out Administration:Admin Fees:Management Fees:Administration: Other Advertising:LiteratuBusiness Cards:Marketing:Advertising: Other Bad Debts:Uncollectable Rec'ls:Bad Debts: Other Delivery Expense:Courier Fees:Freight:Postage:Delivery Expense: Other ... ...where each row is a horizontal dynamic range defined using OFFSET with the name of the value in ColA (minus spaces). These list in the DV dependant on what 'Expense Category' is selected in its DV. (Changing the parent DV after selecting in the dependant DV 'flags' the latter to indicate the cell contents are not found in the 'parent' range) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List from a Dynamic Named Range on Another Workshe | Excel Worksheet Functions | |||
Data Validation drop-down width, with named range source (XL03 and | Excel Worksheet Functions | |||
Data validation drop downs don't recognize dynamic named range | Excel Discussion (Misc queries) | |||
Dynamic Named Range inside a Data Validation list ? | Excel Programming | |||
How can I use a named range for "pick from drop-down list"? | Excel Discussion (Misc queries) |