How to combine two dynamic named ranges (named formulas)

[KBSpec]

Hi All,

I have two dynamic lists I have created using the Named Formula capability. I would like to create a master range by somehow adding the two together so the combination can be used as a pull down list. I don't want to use VBA and would like to again use the Named Formula for the master range. I've tried to search other posts for this, but don't seem to have the correct vocabulary to find the answer to this problem.

Any help will be greatly appreciated.

 

[Jerry Sullivan]

Hi KBSpec,

You can create a DNR that is the union of two other DNRs; however Excel will consider that to be a multi-area range which can't be used as a source for a Data Validation list.

To union DNRs, make a new DNR that Refers to: =dnrMyRange1,dnrMyRange2

 

[KBSpec]

I'm not sure what I'm doing wrong. I can use both of my lists individually as drop down lists, but can't seem to combine them.

list one is TestTypeList={"All";"Alcohol";"FIO";"Product";"Water"}
list two is Sheet3!SampleList={"Alcohol-70/30_IPA";"FIO-7.5_Sterile_Gloves";"FIO-Extrusion_Port";"FIO-Killion";"Product-SLS-";"Water-B5_Return";"Water-B5_Supply";"Water-DI_3_Return";"Water-DI_3_Supply";"Water-Killion";""}

When I put =TestTypeList,Sheet3!SampleList into a cell it comes back #Value with a information response of a value used in the formula is of the wrong data type.

Any suggestions?

 

[Jerry Sullivan]

To clarify my first reply, you can create a union of two dynamic named ranges, however that creates a multi-area range.

Some Excel functions can accept multi-area ranges. For example, try...
=COUNTA(TestTypeList,Sheet3!SampleList)

or if the values are numeric, try
=SUM(TestTypeList,Sheet3!SampleList)

Other Excel functions won't work with multi-area ranges for example, this will return an error.
=ROWS(TestTypeList,Sheet3!SampleList)

Similarly, a Data Validation list won't accept a multi-area range reference (regardless of whether it is a dynamic or static reference).

 

[mikerickson]

Jerry, you can also use Name1:Name2 to combine two named ranges into a single area, but its utility for Validation is limited.

If you have two named ranges List1 and List2, the named range
Name: oneToN RefersTo: =ROW(INDIRECT("1:"&(ROWS(List1)+ROWS(List2))))

Name: mixedList RefersTo: =IF(oneToN<=ROWS(List1),INDEX(List1,oneToN,1), INDEX(List2,oneToN-ROWS(List1),1))

will combine the two and the array formula =MixedList will return the expected values, but putting it into a Validation list will return an error.

The best I can suggest is to put
=MixedList in F1:F1000 (many cells will show #N/A

And use the Validation formula =OFFSET(F1,0,0,Rows(List1)+Rows(List2),1)

 

[Jerry Sullivan]

Mike, Yes Name1:Name2 can also be used to return a single area; but except for some scenarios (eg the names refer to lists in adjacent columns with same start and end rows), that single area will include some cells that are not members of either individual list.

I suspected that a formula approach could be used to build a single list. Thanks for sharing that construction.

Using that approach I was able to display MixedList in column F, but was surprised to see the results of trying to apply the INDEX function to MixedList.

I expected this would return the 5th element in the list (the value displayed in F5)....
=INDEX(MixedList, 5)

Instead it returned #VALUE! whether the formula was array-entered or not.

Using the Evaluate Formula audit tool shows MixedList elements as:
{"A";"A";"A";#VALUE!;#VALUE!;#VALUE!;#VALUE!}

In this example List1 was "A","B","C".

Can you explain that?