Creating a named range that will refer to the total range of multiple other named ranges.

[Simon_D]

For example

I name A2:A5; "test_a"
I name A6:A10; "test_b"

I want to create a third named range "test_c" which then includes the {sum} area of "test_a" and "test_b"

Context / Rationale

I tried to create a named range that includes many (80 plus) non-contiguous named ranges.
Strangely even though i selected all of these cells, and created a named range from that; when i then tried to select the named range - It only selected a partial part of the range. I believe (correct me if i am wrong) that there is character limit within a named range; because when i review the named range (in name manager) it only shows some of the range i had selected.

I thought that a workaround would be the following;

Select part A of the range (such that the total named range does not exceed the character limit)
names this "Test_a"
Select part B of the range
name this "Test_b"

Create another name range which combines test_a and test_b

Solution / Alternative
How would i do that or if not, can you think of any other workarounds?

Limitations
Unfortunately restructuring my data is not really an option
To suit client needs, i also need to avoid any VBA where possible.

I hope that is clear. Many thanks in advance for you help. As my first post, it is an honour to be amongst experts and please forgive any beginner posting errors!

Simon

 

[Ron Coderre]

Try defining test_c as this:

=(test_a,test_b)

Does that hep?

 

[Simon_D]

Try defining test_c as this:

=(test_a,test_b)

Does that hep?

Thank You

Unfortunately that does not seem to work. This, indeed was what i first tried.

The named range EXISTS in name manager as if it was created....

But when i dropdown to select a name range - it does not appear
And when i try GO TO - it also does not appear here.

 

[Ron Coderre]

Try typing the name into the Go_to window. That should work...Does it?

 

[Simon_D]

Try typing the name into the Go_to window. That should work...Does it?

Per my prior post - unfortunately it also does not show in the "Go To" window.

 

[Ron Coderre]

Range names that refer to complex references DO NOT DISPLAY in the Go_To window list.
However, the name exists AND can be manually entered in the input section or referenced via VBA.
Try it.

 

[Simon_D]

Range names that refer to complex references DO NOT DISPLAY in the Go_To window list.
However, the name exists AND can be manually entered in the input section or referenced via VBA.
Try it.

INCREDIBLE - It worked! Sorry that i didn't quite understand your previous post.

What a odd quirk! So strange that "Go to" does not display the option and yet you can still enter it.
I suppose "complex" in the sense or range defined by another range.

It its own small way this will be a game changer for me. i really hope this post remains and is findable by anyone who wishes to do the same. Googling came up pretty dry for me (unless i didnt use the right terms)

Thanks again; a real privilege to get your help.