Named ranges- how do I "combine" several into a larger named range?

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

I started with a large range (1x300 cells) that represented 3 sections of
data that is graphed- but I've since found out that my (internal) customer
sometimes needs to suppress one or two of the sections, so the graph doesn't
show large empty sections when there is no data.

I'm having trouble with the named range(s);
Instead of
NamedRange1 = MySheet!A1:A30

I now have
NamedRange1a = MySheet!A1:A100
NamedRange1b = MySheet!A101:A200
NamedRange1c = MySheet!A201:A300

Now I need a statement that puts all three of these together into one range
to feed to the graph, like: (watch for wrap)

NamedRange1Total =
MyWorkbook.xls!NamedRange1a,MyWorkbook.xls!NamedRange1b,MyWorkbook.xls!Named
Range1c

but that doesn't seem to be working- Help!

(after I get the ranges "merged", I can put in if/then logic to control
whether or not each range is added....)

Many, many thanks in advance!
Keith
 
The problem is that I need to be able to dynamically include or exclude the
three portions of the range, not just write the whole range directly.
Ultimately, this new named range with my control logic needs to be
"something like"
=if (D45=1,NamedRange1a,""),if (D46=1,NamedRange1b,""),if
(D47=1,NamedRange1c,"")

that way I can end up with ranges that refer to regions like:
MySheet!A1:A300 or
MySheet!A1:A100 or
MySheet!A1:A100,MySheet!A201:A300 or
MySheet!A101:A200 or
MySheet!A1:A200

and so on...

but for starters, I can't even get the three smaller named ranges to combine
together within a named range statement, and I need to get that working
first...
Any other ideas?
 
Hi Keith

if you don't have to create ranges like A1:A100 + A201:A300 you may try
the following name definition:
Lets say cell D1 stores a value between 1 and 3 then define the
following name:
Name_Range: =OFFSET('MySheet'!$A$1,0,0,$D$1*100)

this would create the following ranges:
A1:A100
A1:A200
A1:A300

If you also need a changing starting point use the additional cell E1
(0,1,2) and define the name with
=OFFSET('MySheet'!$A$1,$E$1*100,0,$D$1*100)
This would also allow the ranges
A101:A200
A101:A300
A201:A300

It is not possible with this OFFSET function to get a 'combined range'
like A1:A100 + A201:A300 but maybe the above is sufficient for you
 
Keith,

Give this a try


Union(Range("NamedRange1a"), Range("NamedRange1b"),
Range("NamedRange1c")).Name = "NamedRange1Total"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I never said I was overly bright, but I don't understand why you can't do
what Arvi suggested.
You can keep your *original* 3 names in *addition* to the new name:

There's *no* rule that says that a range can't have *more then one name*.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



The problem is that I need to be able to dynamically include or exclude the
three portions of the range, not just write the whole range directly.
Ultimately, this new named range with my control logic needs to be
"something like"
=if (D45=1,NamedRange1a,""),if (D46=1,NamedRange1b,""),if
(D47=1,NamedRange1c,"")

that way I can end up with ranges that refer to regions like:
MySheet!A1:A300 or
MySheet!A1:A100 or
MySheet!A1:A100,MySheet!A201:A300 or
MySheet!A101:A200 or
MySheet!A1:A200

and so on...

but for starters, I can't even get the three smaller named ranges to combine
together within a named range statement, and I need to get that working
first...
Any other ideas?
 
Thank you all for your various replies. Having a break in my range(s) was
necessary, so I wasn't able to use the offset function, but Bob's post got
me thinking about how I was doing the union. I think the issue was that I
was including the workbook name in the union, where all I needed was:
[NamedRange1Total] = NamedRange1a,NamedRange1b,NamedRange1c
I also added a new named range [Z] = B1 (a blank cell)

I then took and referenced some cells to find my final range;
[NamedRange1Total] =
if(D1=1,NamedRange1a,Z),if(E1=1,NamedRange1b,Z),if(F1=1,NamedRange1c,Z)

replicated this logic for my X axis labels, and everything works great!
Thanks again for the replies,
Keith
 
Back
Top