Using a formula (=OFFSET) in a named array?

[construct]

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Suggestions?

[romperstomper]

Please Login or Register  to view this content.

[construct]

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?

[romperstomper]

Oh, I think I see what you mean:

Please Login or Register  to view this content.

? Assumes your sheet names are valid range names.

[construct]

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?

[romperstomper]

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.

[construct]

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

[romperstomper]

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.

[construct]

Worked perfectly. Really appreciate the help romperstomper

+rep

[romperstomper]

Glad to help.