Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation List from a Dynamic Named Range on Another Workshe Jeremy Excel Worksheet Functions 5 March 29th 10 09:26 AM
Data Validation drop-down width, with named range source (XL03 and ker_01 Excel Worksheet Functions 4 March 24th 10 04:31 PM
Data validation drop downs don't recognize dynamic named range GlenC Excel Discussion (Misc queries) 0 July 19th 06 06:25 PM
Dynamic Named Range inside a Data Validation list ? Richard[_34_] Excel Programming 5 March 11th 06 02:58 PM
How can I use a named range for "pick from drop-down list"? InstantZen Excel Discussion (Misc queries) 7 May 24th 05 12:51 AM


All times are GMT +1. The time now is 10:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"