Steve_Brady
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
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
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
>>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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
• 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.
ASKER
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.
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.
ASKER
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.
FYI, I intended to mark ProdOps' post as the best answer and award 500 points to him/her.
ASKER
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.
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.
Patrick
ASKER
>>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.
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.
Check the Name Manager... .does it include all the groups you expected separated by commas?