Combining named ranges into one Master Range for validation

[gvanbee]

Hello:

I have searched the archive, and the net, and can not find a solution to my problem. I know someone on this board knows a solution.

I have several different named ranges that are used for validating a column. I need to provide in a seperate column a validation that combines all of the seperate ranges into one.

NamedRange1 = a1:a3 {a1,a2,a3}
NamedRange2 = a4:A6 {a4,a5,a6}

where a1, a2, a3... are references to the cells that are the value.

What I need is the syntax to define a new range name such as:

CombinedRange = NamedRange1 + NamedRange2
which would not be a mathmatical addition, but a combined list such as:

CombinedRange = A1:A6 {a1, a2, a3, a4, a5, a6}
This message was edited by gvanbee on 2002-10-02 14:13
This message was edited by gvanbee on 2002-10-02 14:14
This message was edited by gvanbee on 2002-10-02 14:18

 

[gvanbee]

Maybe I confused the issue with too much text before, so I will restate the question:

I have this:

NamedRange1 = a1:a3
NamedRange2 = a4:A6

Does anyone know the syntax to achieve this with a defined named range?:

CombinedRange = A1:A6

 

[Damon Ostrander]

Hi gvanbee,

I assume you want VBA code to do this. I believe the following line of code will do what you describe:

Application.Union([NamedRange1], [NamedRange2]).Name = "CombinedRange"

where the two named ranges must be on the active worksheet. If not on the active worksheet, then you should use:

With Worksheets("Sheet1")
Application.Union(.[NamedRange1], .[NamedRange2]).Name = "CombinedRange"
End With

 

[gvanbee]

I was hoping to do it without VBA, as the sheet I am seeting up do not know VBA. I wanted to set up ranges that they could change to change the validation.

If no one has a non-VBA solution, I will be able to use yours to combine the ranges.

Thanks for your answer.

 

[Yogi Anand]

Hi gvanbee:

If I have understood you correctly, you can select A1:A6 and key-in CombinedRange in the name Box and that should do it. See the worksheet simulation ...

y021002.xls
	A	B	C	D	E	F	G	H
1	range1-1				Given:
2	range1-2				NamedRange1isA1:A3
3	range1-3				NamedRange2isA4:A6
4	range2-1
5	range2-2				HighliteA1:A6andinthenameboxkey-in
6	range2-3				CombinedRange
7
Sheet5

</SPAN>

Regards!

Yogi

 

[gvanbee]

Yogi:

Thanks for the post, and I wish it was that easy. The actual ranges that I am using are discontinuous (e.g. A1:A15, C1:C12,D1:D5, etc.), and each range may change in size. I need the CombinedRange to then be the combination of the multiple defined areas.

I assumed that I could go to the insert name define and add a definition for CombinedRange and in the refers to section put in an equation, but when I put DefineRange1 + DefinedRange2, it does not make a combined list, but creates a list that is the mathmatical addition of {A1 to A4, A2 to A5, and A3 to A6}.

Hopefully my explanation is clear.

 

[Yogi Anand]

On 2002-10-02 22:19, gvanbee wrote:
Yogi:

Thanks for the post, and I wish it was that easy. The actual ranges that I am using are discontinuous (e.g. A1:A15, C1:C12,D1:D5, etc.), and each range may change in size. I need the CombinedRange to then be the combination of the multiple defined areas.

I assumed that I could go to the insert name define and add a definition for CombinedRange and in the refers to section put in an equation, but when I put DefineRange1 + DefinedRange2, it does not make a combined list, but creates a list that is the mathmatical addition of {A1 to A4, A2 to A5, and A3 to A6}.

Hopefully my explanation is clear.

Hi gvanbee:

While holding the CTRL key
Highlite A1:A15, C1:C12, D1:D5
Then INSERT|NAME|DEFINE -- CombinedRange -- OK

The CombinedRange would then consist of:
=Sheet9!$A$1:$A$15,Sheet9!$C$1:$C$12,Sheet9!$D$1:$D$5

Mine is Sheet9 -- please adjust your Sheet name to suit.
Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-02 23:09

 

[gvanbee]

Excellent Yogi!

You answered my question indirectly. Your solution would not update the combined range when the other named ranges were redefined.

Using your syntax, the syntax for my problem is:
Then INSERT|NAME|DEFINE -- CombinedRange --
refers to: =NamedRange1,NamedRange2,NamedRange3
OK

The CombinedRange would then consist of:
=NamedRange1,NamedRange2,NamedRange3

Through the defined names, and not the absolute reference.

I just needed commas between the different ranges in the definition, I kept using +, and &.

Thanks again!!!!!
This message was edited by gvanbee on 2002-10-03 08:03

 

[RoyalFlesh]

Hello,
I thought that using the instructions from this thread would solve my problem but it didn't...

What I need :
is a NamedRangeZ that I could use as a list for data validation.
NamedRangeZ would consist of a NamedRangeA (few rows, single column) and additionally two constants: {"all","not applicable"} - ideally..but if no chances - then instead of the array constant I could use a NamedRageB on a hidden sheet with just these two values.

What the problem is :
that after I manage to combine NamedRangesA and NamedRangesA into NamedRangeZ and I try to use it as a list in data validation criteria I receive the error message : " The source list must be a delimited list, or a reference to a single row or column ". When I try to use both NamedRageA and NamedRageB as the source than it says "You may not use unions, intersections, or array constants for Data Validation criteria".

These warning messages make me think that it is simply not possible to get what I want.. but it sounds like gvanbee did it. So what am I missing?

(NamedRangeA is a single column part of another,bigger and dynamic named range - so I don't want values "all","not applicable" to be put there)

(Excel 2003)

 

[Misty97]

RoyalFlesh - did you get a reply or a solution? I have the same problem!

Thanks