How Do You Call Several Named Ranges From A Named Range

[Minitman]

Greeting,

I have over 100 cells that I need to go to in a set order. I tried to
choose them with the ctrl button held down and clicking on each, but
that is only good for about 30 or so. I went ahead and set up 5 named
ranges. Now I can't quite figure out how to get them to come up one
after another. They are R1, R2, R3, R4, and R5.

Anyone have any ideas?

TIA

_Minitman

 

[Charles Williams]

define R15 as =R1,R2,R3,R4,R5


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[David McRitchie]

How is a range that looks like a cell name going to work.
Is this a worksheet question or a programming question
-- sure can't tell from looking at the newsgroup, since you have more than one.

 

[Minitman]

Hey Charles,

I just tried that. It looks like it Excel took it. And it works
perfectly. The only problem is that you cannot see the new range name
from the drop down menu called Name Box. I got around that by using
Private Sub Worksheet_Activate() in the sheet that I wanted this to
work.

Someone asked if this question was programming or worksheet related.
It is both.

Thank you for filling in this gap in my knowledge, It is much
appreciated.

-Minitman

 

[Minitman]

Hey David,

That is a good question. I don't know weather it is a programming or
a worksheet function either. It turns out that it is both.

When I've asked this question in the past, I've been told that it
can't be done on the worksheet, only with VBA. This seems to be a
way, but I could not make it work.

If you are looking for a way to set the tab order on a bunch of cells
that are not touching each other, check out Charles response and my
reply.

-Minitman

 

[Charles Williams]

If you are doing a lot of work with named ranges you might want to download
Name manager from my downloads page (jointly developed by Jan Karel Pieterse
and myself with Mac localisation by Matthew Henson).

It will show you this kind of name

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[RagDyer]

Would you please tell me what version of XL you're using.
I can't picture any version that I've used accepting a range name of "R1".

In fact, I've just tried it again in XL2k and XL97, anf it was rejected in
both.
I'll have to wait until tomorrow to try it in XL02 again.
I even tried [space]R1, and 'R1, with those also rejected.

I'm very curious as to how you got those range names accepted.
--


Regards,

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

Hey Charles,

I just tried that. It looks like it Excel took it. And it works
perfectly. The only problem is that you cannot see the new range name
from the drop down menu called Name Box. I got around that by using
Private Sub Worksheet_Activate() in the sheet that I wanted this to
work.

Someone asked if this question was programming or worksheet related.
It is both.

Thank you for filling in this gap in my knowledge, It is much
appreciated.

-Minitman

 

[David McRitchie]

I reread the question and answers based on your question.
I guess you were just giving examples so R1, R2, etc were not
the actual names so question should be interpreted as a concept
not as actual named ranges. Because you can't have those as
named ranges. That is what was confusing.

I hadn't noticed the words tab order (as in Tab Key) before your last reply.

You can also affect cell navigation using sheet protection to keep you
out of locked cells.
http://www.mvps.org/dmcritchie/excel/protection.htm
http://www.mvps.org/dmcritchie/excel/navigation.htm

 

[RagDyer]

Now that you mention it David (giving examples so R1, R2, etc were not
the actual names), it does seem kind of obvious, doesn't it?
I feel a little silly dwelling on it!

Anyway, to "Minitman",
The "combined" named range, i.e."R15", even though not visible in the name
box, can still be easily invoked FROM the name box by simply entering "R15"
(no quotes) into the name box and hitting <Enter>.
--

Regards,

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


I reread the question and answers based on your question.
I guess you were just giving examples so R1, R2, etc were not
the actual names so question should be interpreted as a concept
not as actual named ranges. Because you can't have those as
named ranges. That is what was confusing.

I hadn't noticed the words tab order (as in Tab Key) before your last reply.

You can also affect cell navigation using sheet protection to keep you
out of locked cells.
http://www.mvps.org/dmcritchie/excel/protection.htm
http://www.mvps.org/dmcritchie/excel/navigation.htm

 

[RagDyer]

It's a mute point now, after David switched on the lightbulb over my head.

BTW, have you tried entering NR_All into the name box to envoke the ranges?
--


Regards,

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

Hey Rag,

Sorry, I shortened the name. What I used was NR1 though NR9 with the
resultant range called NR_All. I am using XL 2K on Windows 2K.

-Minitman

 

[Minitman]

Hey Rag,

Sorry, I shortened the name. What I used was NR1 though NR9 with the
resultant range called NR_All. I am using XL 2K on Windows 2K.

-Minitman

 

[Minitman]

Thanks, I'll give that a try.

-Minitman

 

[Minitman]

Hey David,

To all who responded,

Thank you all for the assistance, it is most appreciated.

-Minitman

 

[David McRitchie]

I think it was Charles Williams though that provided your
answer, I couldn't make out the question or the his answer
until at the time. Though I don't expect you will be ever expecting
to use named ranges that look like cell names now that it's been
pointed out to you. A common mistake for the rest of us.