Dependent Dynamic Drop Down List - Name Manager - Cell Named to Array

[mginsburg88]

Hello,

I am trying to create a cell range that references an array in an effort to avoid blanks in the dropdown. The formula is:

=Legend!$M$3:INDEX(Legend!$M$3:$M$38,SUMPRODUCT(--(Legend!$M$3:$M$38<>"")))

Only cells M3-M7 have data and I want to avoid the rest of the blank cells from showing up on my list. When I do this outside of the name manager and hit CTRL-SHIFT-ENTER, the formula works. The issue is that I cannot do this in name manager. Do I need to change my formula?

Thanks in advance. I've spent way too many hours trying to figure this out.

Mike

 

[XOR LX]

Hi.

For a start, that formula does not require CTRL+SHIFT+ENTER.

Secondly, it works fine for me within Name Manager. As for then using that Defined Name as the source for a Data Validation list, that also should be fine, providing you choose the List option and then enter the name of your Defined Name within the Source box (preceded by an equals sign, of course).

Perhaps the issue you were having was that you were actually attempting to bypass the Defined Name part and instead enter that formula directly into the Data Validation? If so, then, yes, that will be disallowed.

Regards

 

[mginsburg88]

Hi.

For a start, that formula does not require CTRL+SHIFT+ENTER.

Secondly, it works fine for me within Name Manager. As for then using that Defined Name as the source for a Data Validation list, that also should be fine, providing you choose the List option and then enter the name of your Defined Name within the Source box (preceded by an equals sign, of course).

Perhaps the issue you were having was that you were actually attempting to bypass the Defined Name part and instead enter that formula directly into the Data Validation? If so, then, yes, that will be disallowed.

Regards

When I click on the Refers to: field that had the formula, it does register the cells but the value under the name is {...}.

I am doing a reference to the name of this cell using this formula: =INDIRECT(SUBSTITUTE($E$2," ","")) E2 is the name of the reference. Some of them have spaces and some dont.

 

[XOR LX]

I am doing a reference to the name of this cell using this formula: =INDIRECT(SUBSTITUTE($E$2," ","")) E2 is the name of the reference. Some of them have spaces and some dont.

I'm not sure to what you're referring now? You seem to be mentioning completely separate things. So does or doesn't the data validation function as I laid out?

Regards

 

[mginsburg88]

I'm not sure to what you're referring now? You seem to be mentioning completely separate things. So does or doesn't the data validation function as I laid out?

Regards

The way you had it laid out is what I am doing and it is not working.

Let me set out an example. It is kindof confusing.

[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 2"]Category[/TD]
[TD]Apple[/TD]
[TD]Pepper[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD][/TD]
[TD]Red[/TD]
[TD]Orange[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Pepper[/TD]
[TD][/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to set the Color column to depend on another cell. A1 is the dropdown of Category all the way down to 1,000. The formula for the category named ranged is :=Legend!$A$3:INDEX(Legend!$A$3:$A$1000,SUMPRODUCT(--(Legend!$A$3:$A$1000<>""))). The dropdown for category references the category named range This works perfectly.

This issue I am having is the second dropdown for Color. This needs to change based on the category dropdown in A1. The formula in this dropdown is: =INDIRECT(SUBSTITUTE($A$1," ","")) because some of my future categories might have a space in between and named ranges can't have a space. I have a named range for each category with the same formula: =Legend!$B$3:INDEX(Legend!$B$3:$B$1000,SUMPRODUCT(--(Legend!$B$3:$B$1000<>""))) for Apple, =Legend!$C$3:INDEX(Legend!$C$3:$C$1000,SUMPRODUCT(--(Legend!$C$3:$C$1000<>""))) for Pepper, and =Legend!$D$3:INDEX(Legend!$D$3:$D$1000,SUMPRODUCT(--(Legend!$D$3:$D$1000<>""))) for grape. My dropdown is blank when I do this.

 

[XOR LX]

You could've mentioned that you were referencing these Names indirectly in your first post!

You need to use EVALUATE, not INDIRECT, in such cases, which will necessarily mean that your workbook will need to be macro-enabled. (Even though no direct VBA is actually entered, EVALUATE is technically an XLM function.)

So you would need to create a further Defined Name, e.g. Val, viz:

Name: Val
Refers to: =EVALUATE(SUBSTITUTE($A$1," ",""))

and then for the Data Validation you would select List and enter:

=Val

in the Source box.

Regards

 

[mginsburg88]

You could've mentioned that you were referencing these Names indirectly in your first post!

You need to use EVALUATE, not INDIRECT, in such cases, which will necessarily mean that your workbook will need to be macro-enabled. (Even though no direct VBA is actually entered, EVALUATE is technically an XLM function.)

So you would need to create a further Defined Name, e.g. Val, viz:

Name: Val
Refers to: =EVALUATE(SUBSTITUTE($A$1," ",""))

and then for the Data Validation you would select List and enter:

=Val

in the Source box.

Regards

Thanks you so much!!! You're a lifesaver.

 

[XOR LX]

You're welcome!

Cheers