Dependent drop down dynamic range formula, off sheet.

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

Dependent drop downs on Sheet1.
First drop down in B2 & source is =Division_List
Second drop down in C2 & source is INDIRECT(B2)

Division_List is a list of 16 named ranges DIVISION_1 , _2 , _3 ... _16. And these are on another sheet.

Those names are the Headers of 16 columns on sheet CSI_DETAILED with each list row 2 to a variable row.
All work fine with non dynamic formulas for the ranges.

Want to swat to dynamic ranges for each DIVISION_n

This refers to named range DIVISION_1 on the sheet CSI_DETAILED.

=OFFSET(CSI_DETAILED!$D$2,0,0,COUNTA(CSI_DETAILED!$D:$D),1)

Does not evaluate to an error but the value in the name manager = (...).

Works in a sample drop down on the CSI_DETAILED sheet, shorter or longer list is picked up in the test drop down.

Should I be remembering that you cannot use the offset formula from another sheet for this?

I googled plenty of samples and the formulas are the same, but no mention about other sheets/off sheet.

Thanks,
Howard
 
Hi Howard,

Am Fri, 19 Dec 2014 00:17:54 -0800 (PST) schrieb L. Howard:
Dependent drop downs on Sheet1.
First drop down in B2 & source is =Division_List
Second drop down in C2 & source is INDIRECT(B2)
Click to expand...

in data validation you cannot refer to other sheets. You have to use
global names (workbook scope)
If it does not help please send me the file


Regards
Claus B.
 
Hi again,

Am Fri, 19 Dec 2014 00:17:54 -0800 (PST) schrieb L. Howard:
Dependent drop downs on Sheet1.
First drop down in B2 & source is =Division_List
Second drop down in C2 & source is INDIRECT(B2)
Click to expand...

the depending list must have a fix range. It will not work with dynamic
range names


Regards
Claus B.
 
Hi again,

Am Fri, 19 Dec 2014 10:04:47 +0100 schrieb Claus Busch:
the depending list must have a fix range. It will not work with dynamic
range names
Click to expand...

if you want dynamic names in CSI_DETAILED you have to do it with VBA.
In a standard module:

Sub myNames()
Dim LCol As Long, LRow As Long
Dim i As Long

With Sheets("CSI_DETAILED")
'Counts the columns in row1. Modify to suit
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LCol
LRow = .Cells(Rows.Count, i).End(xlUp).Row
ThisWorkbook.Names.Add .Cells(1, i), _
RefersTo:=.Range(.Cells(2, i), .Cells(LRow, i))
Next
End With

End Sub

In sheet module of sheet "CSI_DETAILED":

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:J100")) Is Nothing _
Then Exit Sub

Call myNames
End Sub

If you change the data in sheet "CSI" the macro myNames will run and
change the range of the names


Regards
Claus B.
 
Hi again,

Am Fri, 19 Dec 2014 10:04:47 +0100 schrieb Claus Busch:


if you want dynamic names in CSI_DETAILED you have to do it with VBA.
In a standard module:
Click to expand...
Okay, thanks Claus.

I will work with the event code you posted, I already have one going on the sheet.

Will see what I can do.

I have to be away from my computer for awhile, but I will get back for more help or to let you know I got it going.

Thanks again.
Howard
 
Hi Howard,

Am Freitag, 19. Dezember 2014 11:49:02 UTC+1 schrieb L. Howard:
I have to be away from my computer for awhile, but I will get back for more help or to let you know I got it going.
Click to expand...

if you need help please send me a mail. My news Server is down.

Kind Regards
Claus
 
the depending list must have a fix range. It will not work with
dynamic
range names
Click to expand...

Not true, Claus! I use dynamic ranges for all DV lists so they
auto-adjust for additions/deletions. Dependant lists still work with
INDIRECT(). Have a look here...

https://app.box.com/s/23yqum8auvzx17h04u4f

...at IncomeExpense.xlt!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top