Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Excel omits some cells when naming ranges

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
Steve_Brady
Asked:
Steve_Brady
1 Solution
 
NBVCCommented:
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
 
Steve_BradyAuthor Commented:
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
 
Jerry PaladinoCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Steve_BradyAuthor Commented:
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
 
Steve_BradyAuthor Commented:
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
 
Steve_BradyAuthor Commented:
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
 
Steve_BradyAuthor Commented:
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
 
Patrick MatthewsCommented:
Steve,

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

Patrick
0
 
Steve_BradyAuthor Commented:
>>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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now