Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel omits some cells when naming ranges

Posted on 2013-05-31
11
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 

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 93

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

670 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