Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

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
Avatar of NBVC
NBVC
Flag of Canada image

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?
Avatar of Steve_Brady

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Jerry Paladino
Jerry Paladino
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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.
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:

https://www.experts-exchange.com/questions/28144316/Excel-omits-some-cells-when-naming-ranges.html?anchorAnswerId=39213203#a39213203

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

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

Patrick
>>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.