Name Ranges

[alainfranco]

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.

Many thanks

 

[SpillerBD]

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]

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]

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.

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