Solved

Excel omits some cells when naming ranges

Posted on 2013-05-31
11
221 Views
Last Modified: 2013-06-10
Hello,

When assigning names in Excel (2010), either in the Name Box or Name Manager, what limitations exist for combining previously named groups of cells?

For example, suppose you randomly select some number of cells — some of which are grouped and some of which are single — and assign the selected cells the name Group_1. Then, suppose you repeat the process until you have named different combinations of cells Group_2, Group_3 & Group_4.

Now, using the drop-down menu from the Name Box, you can select one of the groups, say Group_1. And then, by holding down the Ctrl key and using the same drop-down menu, you can also select Groups 2, 3 & 4 so that all groups are selected. Next, suppose you assign this combined selection the name,  Group_All.

It has been my experience in this scenario that (after the above-mentioned groups are all unselected) opening the Name Box drop-down menu and selecting Group_All does not result in selection of all previously included cells. Rather, one or more of the sub-groups or even a portion of one or more subgroup remains unselected.

Trying to determine the reason for that behavior and what determines which cells are selected vs. which remain unselected is the object of this question.

Thanks
0
Comment
Question by:Steve_Brady
11 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39211494
Unfortunately, I don't have 2010 or later versions at my current disposal... but I tried it in 2007 and it worked just fine for me.... Group_All selected all the subgroups properly...

Check the Name Manager... .does it include all the groups you expected separated by commas?
0
 

Author Comment

by:Steve_Brady
ID: 39212232
Thanks for the response.

>>I don't have 2010 or later versions

I believe that should not be a factor (but stranger things have happened).  :)


>>>Check the Name Manager

Yes, I did that and as expected, the corresponding references are missing. In other words, the references for ranges that are excluded when the combined name is clicked, are also missing in the Name Manager.


Attached is a workbook with all data deleted. You will notice that three cell ranges have been defined and given the names:

        Hdngs1
        Hdngs2
        Hdngs3

Please try combining them into a single named range as described above.

Each time I try it, the resulting name (I use "All") is accepted. However, when I then select "All" from the Name Box, part of the range (as it was defined) is not selected.

Incidentally, I noticed that the missing ranges in "All" are usually from the last range I enter when selecting the ranges to be included.

For example, if I select:

    Hdngs1 > {Ctrl Down} > Hdngs2 > Hdngs3 > {Ctrl Up}

then the ranges missing from "All" are from Hdngs3.  On the other hand, if I select:

    Hdngs2 > {Ctrl Down} > Hdngs3 > Hdngs1 > {Ctrl Up}

then the missing ranges are from Hdngs1.

That makes me wonder if perhaps there is a limit on how many ranges can be combined to form a larger range. I have googled around a bit to see if that's correct but have found nothing. I will be interested in learning what you observe when you attempt to combine the ranges in my attachment.

Thanks again.
2013-05-31-EE-combining-name-gro.xlsx
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39212831
In Excel 2010...

(1) - Select all three ranges and define the new name "ALL" using the menus (Formulas / Define Names / Define Name) and then enter "ALL" as the name.   This generates a defined name that includes all the 3 ranges.

(2) - Select all three ranges and defined the new name "ALL" by typing ALL in the range box to the left of the formula bar.   This results in the last range group selected being eliminated from the "ALL" range.  Only the first two are included.

So, yes - the problem can be duplicated in 2010 and it appears the solution is to use the Define Name menu option to create the combined defined name.
0
 

Author Comment

by:Steve_Brady
ID: 39213195
Many thanks.

• It's curious that the new name is accepted correctly when going through the formula menu even though it is not when using the Name Box

• I'm relieved to learn that there is no limit on how many ranges can be defined as a new range.

• I'm very happy to have a solution.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Steve_Brady
ID: 39213869
I've requested that this question be closed as follows:

Accepted answer: 0 points for Steve_Brady's comment #a39213195

for the following reason:

Many thanks.

• It's curious that the new name is accepted correctly when going through the formula menu even though it is not when using the Name Box

• I'm relieved to learn that there is no limit on how many ranges can be defined as a new range.

• I'm very happy to have a solution.
0
 

Author Comment

by:Steve_Brady
ID: 39213203
Oops! I inadvertently selected my own comment to receive points. That was not my intent but now I can't figure out how to go backwards to fix it. I will try to get some help.

FYI, I intended to mark ProdOps' post as the best answer and award 500 points to him/her.
0
 

Author Comment

by:Steve_Brady
ID: 39213217
Just sent the following email to be tech support:

Oops! I inadvertently selected my own comment to receive points. That was not my intent but now I can't figure out how to go backwards to fix it.

The thread is at:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28144316.html#a39213203

FYI, I intended to mark ProdOps' post as the best answer and award 500 points to him/her.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39213768
Steve,

If you post an "objection" it will halt the self-answer request you inadvertently made.

Patrick
0
 

Author Comment

by:Steve_Brady
ID: 39213870
>>If you post an "objection" it will halt the self-answer request you inadvertently made.

Thanks Patrick

==================================================
Hello,

I inadvertently selected my own comment to receive points. That was not my intent but now I can't figure out how to go backwards to fix it.

FYI, I intended to mark ProdOps' post as the best answer and award 500 points to him/her.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now