Solved: Nested Range Names

[SamT]

Is there any system where I can have a Range Name, (ex: Cat_1,) that encompasses 1 or more Columns where Range Name Cat_1_1 is one of those columns.

I want to refer to Cat_1.Cat_1_1 or something like that.

Even if I can do something like Var = Cat1_Group.Names(i) it would help.

I have a situation, accounting, with multiple levels of header titles and I want to be able to Drill Down.

I have considered; checking Fill Colors where each heading's fill colors extend across the columns it relates to; And a left and right border at the ends of its' area.

While there will always be a top level and a bottom level, there might be missing mid-levels. The bottom level headings will always refer to a single column.

The idea is to open a Form that ListBoxes Workbook Names, (accounting type,) Generate a ListBox of Worksheet Names for the selected WB, then do the same with top level Column heads and repeat down to the bottom level names.

Finally, I'll need an info bar, maybe the Title bar, to iterate the qualified location, by name, of the selected column. Ex: Expenses>>Shop>>Cleaning_Supplies>>Rags where "Expenses is the Workbook Name; "Shop," the Sheet Name; down to the Column/Range Name "Rags."

OK, I think I can do that part myself. LOL


Thanks

SamT

[Bob Phillips]

Does


Un ion(Cat_1, Cat_1_1)


do what you want?

[Jan Karel Pieterse]

If Cat_1_1 is completely "surrounded" by Cat_1, why not just use Cat_1_1 directly? Otherwise, you could use:
Cat_1 Cat_1_1 (separated by the space operator)
in VBA:
Intersect(Range("Cat_1"),Range("Cat_1_1"))

[SamT]

XLD and Jan,

Thanks, I already knew some of that, but Cat_1 Cat1_1 is new.

I see that I wasn't very clear.

Say Cat_1 covers 6 columns, Cat_1_1 covers Cat_1(A2:C65k), Cat_1_3, Cat_1(D2:F65k.) Then Cats_1_2, 1_3 each have 3 level 3 categories that go from the 3rd Row down.

Populating a ComboBox from a List is trivial, but a list that has empty cells? Populating the next CBox with the appropriate, category 2 and lower Labels/Names is my problem

Hmmnh, Something in this thread has triggered my last seven brain cells.


While not Intersect(Range(Cat_1), Cells(Cat_1(A1)).offset(1,0))
Populate Cbox
End While


I think something like that pseudocode will work, and just sub Cat_1_1 and lower as indicated.

Thanks guys, for at least waking me up. Sometimes ya jusat gotta tell someone else yer problem. :)