Name Ranges

alainfranco

New Member
Joined
Mar 7, 2012
Messages
38
Hello,

I am trying to get something working that would required Name Ranges to be created automatically based on the content of specific cells.


Specifically, I have a column called GROUP that contains a numerical value (1 to 20) which allows me to "group" items together. I would like to be able to create name ranges based on the value of this cell.

So that, all items with value "1" (for instance) are grouped together in a name range called Group1, and then the same for items with value "2", which would be grouped together in a named range called Group2, and so on so forth.

Is this feasible in excel? If so, could you please point me in the right direction?

Heres is simplified version of my spreadsheet.
Capture.PNG


Many thanks :)
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
Feasible? Yes. Certain difficulties exist that make solution undesirable, most notable is Named ranges refer to absolute cell references which means you would need to have the ranges reevaluated quite often.
It is also somewhat redundant in most situations. Your Group range is the entire range $C$5:$C$14 (in your first sample table) and typically conditional sum/filters.. etc are used on the value. Same thing if data sent to a PivotTable.
 

alainfranco

New Member
Joined
Mar 7, 2012
Messages
38
Feasible? Yes. Certain difficulties exist that make solution undesirable, most notable is Named ranges refer to absolute cell references which means you would need to have the ranges reevaluated quite often.
It is also somewhat redundant in most situations. Your Group range is the entire range $C$5:$C$14 (in your first sample table) and typically conditional sum/filters.. etc are used on the value. Same thing if data sent to a PivotTable.

Hello SpillerBD,

Thanks for your reply.

I understand that it might not be a desirable option, but that is the only one I have at the point where I am.

Were you suggesting that it is possible to modify the name ranges automatically or not possible?

To counter the undesirability of the solution, I wouldn'T might running a macro at file launch to get the name ranges in order before using the spreassheet.

Any help is appreciated! :)

Thanks
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
You would need a macro to find the cells that belong in each "group" and then assign them to your named group. For ease, you could evoke a launch, as cells change or with button. I set some named cells/ranges with a code similar to, but I've modified a little. You would still need to find or write code to return the Array of cells that make up each group (RANGE ARRAY) though.
Code:
ActiveWorkbook.Names.Add Name:="Group1", RefersToR1C1:= _
"=Sheet1!" & RANGE ARRAY

"...but that is the only one I have at the point where I am." I doubt that mostly because I often see many different solutions for the same problem. And the only time some of those aren't workable is because it requires change to layout that exist from "above" (higher management) that isn't as knowledgeable in Excel as the real user (you.) You might see if someone has a flip-side solution that wouldn't require this named-array search...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,690
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top