Stacking dynamic ranges

I have a set of ranges that I need to stack on top of each other in a single column. The number of ranges that need to be stacked will vary (i.e., it is dynamic), and the number of entries in each range will also vary (i.e., is dynamic). My question is: how can I do this?

I am hoping that with the “named range” functionality, a large named range can be created by combining multiple other ranges, but I am not sure how to do this, so any help would be appreciated.

More specifically, this is the scenario I have: I need to create a column that lists all the groups that information will be analysed by. The groups are defined by combining Categories, and Groups within categories. For example, I could have the Categories “Regions” and “Business Units”. Within the “Region” category, I could have three groups, say “Northern”, “Western”, and “Eastern”. In the “Business Units” category, I could have four groups, say “Sales”, “Marketing”, “Finance”, “Human resources”. All these groups then need to be stacked into a single column, which in this example would look like this:

Northern
Western
Eastern
Sales
Marketing
Finance
Human Resources

However, what makes this complicated is that both the number of Categories and the number of Groups within each category is dynamic: In one situation I could have two Categories, in another I could have four Categories. And, in some situations there could be three groups in the first category and five groups in the second category. In another situation there could be ten groups in the first category, and eight groups in the second category.

Regards
Steven

I have in the past done this using formulas, and I remember it was long and arduous. I think your needs would be better served developing a macro. Is this a possibilty, or does it have to be a formula?

oldchippy - thanks for the link. It contains a lot of very useful pieces of information for using dynamic named ranges, so I will definitely bookmark it. However, as far as I can tell what I am trying to do goes beyond what is covered.

BigBas - formulas are not strictly necessary, but it would be a neater solution (as the information in the stacked column will update immediately). Do you remember what types of formulas you used?

However, I suspect that you are correct, though, and that I will need to use a macro for this...

Many thanks!

If these subcatagories are dynamic, how are you modifying your column to increase a catagory stuck between two other catagories; insert rows or retyping everything?

Can you have the catagories initially in separate columns and then have them stacked in a single column via formulas?

ChemistB

Originally Posted by Steven Fleck

BigBas - formulas are not strictly necessary, but it would be a neater solution (as the information in the stacked column will update immediately). Do you remember what types of formulas you used?

If I remember correctly, it was a combination of IF's, OFFSET, COUNTA, and DYNAMIC NAMED RANGES. How many named ranges do you currently have? Do you often add new named ranges entirely?

I know for a fact that I have the file saved on my home computer (I'm at work now). I can also try to work something out for you again if necessary. If possible, post a .zip sample of what you currently have so we have a basis to work from.

I currently have five dynamic ranges, but once I have got the stacking working, I will add quite a few more (possibly up to 20). However, for each use scenario, it is highly likely that a different number of ranges will be used.

I have attached a zipped spreadsheet that illustrates where I have got to so far.

Originally Posted by ChemistB

If these subcatagories are dynamic, how are you modifying your column to increase a catagory stuck between two other catagories; insert rows or retyping everything?

This is the problem - as the subcategories are dynamic, the position of each category (i.e., set of subcategories) in the column will change depending on the contents of each category. Ideally, some syntax will enable a "relative starting point" to list each subcategory - i.e., "begin listing the subcategories of category 2 on the row after the last subcategory of category 1, and so on.

Can you have the catagories initially in separate columns and then have them stacked in a single column via formulas?

I currently have the categories in separate columns, but am not sure which formulas to use in order to stack them (as they are dynamic, and their size will therefore differ).