Drop Down with dynamic named range offsheet source list

[L. Howard]

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/4pyllrq4qo95j4o/Copy of Drop Down Off Sheet Source Lists Drop Box.xlsm

Thanks,
Howard

 

[L. Howard]

I found the solution I needed, using Tables on the source sheet instead of an OFFSET formula.

Howard

 

[Claus Busch]

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.

 

[Claus Busch]

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("A120")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

NewNames

End Sub


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Thu, 22 May 2014 08:57:25 +0200 schrieb Claus Busch:







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("A120")) 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

 

[Claus Busch]

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.

 

[L. Howard]

Hi Howard,



Am Thu, 22 May 2014 04:32:11 -0700 (PDT) schrieb L. Howard:






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/w5f2q0x5dbxf53i/Drop Down Off Sheet Source Lists Tables Drop Box.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

 

[Claus Busch]

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

 

[L. Howard]

that is for your first example where you needed names from column 1 to 4

(A)

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

 

[GS]

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

 

[Claus Busch]

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.

 

[GS]

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

 

[GS]

Hi Garry,

Am Thu, 22 May 2014 12:24:55 -0400 schrieb GS:


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

 

[Claus Busch]

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.

 

[GS]

Hi Garry,

Am Thu, 22 May 2014 12:51:31 -0400 schrieb GS:


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 Incomeiscounts Given
Expense Categories
CoGS:Inventoryiscountsirect Wages:Contracts:Freight In:Freight
Out
Administration:Admin Fees:Management Fees:Administration: Other
Advertising:Literature:Business Cards:Marketing:Advertising: Other
Bad Debts:Uncollectable Rec'ls:Bad Debts: Other
Delivery Expense:Courier Fees:Freightostageelivery 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