Hey,
So I have a loop that finds a worksheet, cat, and then names an array, cat, that is supposed to have the following value.
I know that code works when you input it directly into the name manager, but I'm having trouble creating the loop that will create the array with cat being a variable. This is what I have tried:
:=OFFSET(cat!A2,0,0,COUNTA(cat!$A$2:$A$100),1)
Suggestions?ThisWorkbook.Names.Add name:cat,RefersTo:=OFFSET(cat!A2,0,0,COUNTA(cat!$A$2:$A$100),1)
ThisWorkbook.Names.Add name:="cat", RefersTo:="=OFFSET(cat!A2,0,0,COUNTA(cat!$A$2:$A$100),1)"
Everyone who confuses correlation and causation ends up dead.
Sort of... that doesn't really solve my original problem since it just sets the value of the array to be cat!.... which refers to a sheet named cat and not the variable cat.
Any suggestions?
Oh, I think I see what you mean:
? Assumes your sheet names are valid range names.ThisWorkbook.Names.Add name:=cat, RefersTo:="=OFFSET(" & cat & "!A2,0,0,COUNTA(" & cat & "!$A$2:$A$100),1)"
That works perfectly, but for some reason its screwing with my dependant dropdown
I have 1 dropdown with the value cat and then a second list that is supposed to be the dropdown cat, via =Indirect(a7), but whenever it tries to call the array that I've made nothing comes up?
INDIRECT does not work with dynamic named ranges. You'll need to create another named range that uses EVALUATE
=EVALUATE(Sheetname!$A$7)
and refer to that range name in the DV list.
So the cell I'm using as the second dropdown is in worksheet Main, cell B7, and when I tried to change the source of B7 from =INDIRECT(A7) to =EVALUATE(Main!$A$7) it gives me a formula error message
You cannot use EVALUATE directly in a formula, which is why I said you had to create another defined name that uses that function, then refer to the name in the list. So for example define a name as MyList and in the RefersTo box enter:
=EVALUATE(Main!$A$7)
then in your DV list use =MyList as the source.
Worked perfectly. Really appreciate the help romperstomper
+rep
Glad to help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks