How do I use a range of ranged names.

[derrack]

All the headers are named ranges instead of the whole thing being one named range.

This is sheet DIALOG.

Named Ranges.

Headers
MISC=OFFSET(DIALOG.!$C$3,,,COUNTA(DIALOG.!$C:$C),)
THHN=OFFSET(DIALOG.!$D$3,,,COUNTA(DIALOG.!$D:$D),)
Ect. for all headers..

Table
TABLE=OFFSET(DIALOG.!$A$3:$B$3,,,COUNTA(DIALOG.!$A:$A),)

List of Named Ranges.
TYPE=OFFSET(DIALOG.!$A$3,,,COUNTA(DIALOG.!$A:$A),)


..........1 .......2 .........3 .........4 .......5 ........6 ..........7 ..........8
MISC... THHN.. XHHW.. GRC.... EMT.... PVC.... ........... ...........
text932 text941 text165 text996 text725 text925 ........... ...........
text186 text636 text852 text949 text986 text543 ........... ...........
text321 text590 text564 text222 text718 text151 ........... ...........
........... text607 text248 text689 text719 text339 ........... ...........
........... text530 ........... text226 text811 text728 ........... ...........
........... text845 ........... text288 text731 text145 ........... ...........
........... text430 ........... ........... text737 ........... ........... ...........
........... text875 ........... ........... text449 ........... ........... ...........
........... ........... ........... ........... ........... ........... ........... ...........

These named ranges are all Dynamic so anyone can add more text*** and it will automatically adjust no matter how long or short each column is.

Table

MISC 1
THHN 2
XHHW 3
GRC 4
EMT 5
PVC 6
....... .
....... .
....... .

The table is Dynamic for column B on sheet "1"

This is sheet "1"

Sheet has 100 rows.
Column: a b c
Row 1: a b c
Row 2: a b c
Row 3: a b c

In column B every row has a cell with Data Validation List. For the Source it has
=TYPE

So this cell drops down a selectable list of the named ranges to pick your ranged list choices for column C. This is dynamic so I can type in new named ranges in the table in sheet DIALOG. and it will instantly show up in this column.

In column C every row has a cell with Data Validation List. For the Source it has
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE),MISC,THHN,XHHW,GRC,EMT,PVC)

It checks cell B in its row, in this case row 6, for the named range list to make selectable. Select THHN in B and column C drops down the text*** list for that named range. It is Dynamic so I can add or delete anything under sheet DIALOG.'s THHN column and I will see it here.

What I want is for TYPE, which is the names of the ranges, to be in the code instead of the names of the ranges. So I can add new categories to the main table without adjusting the list validation in cell C on every row.

I want cell C to do.
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE),TYPE)

Since TYPE is dynamic it will update when sheet "DIALOG." TABLE is updated. That way people can follow the pattern on DIOLOG. and not have to redo the data validation lists for every row on sheet "1". They could just type in TAPE under 7 and TAPE 7 under table and the category would be included in the Data list in column C on sheet "1".

Thanks for the help on this. My brains hurting a bit

 

[BiocideJ]

I have to admit, I don't believe I fully understand what you are trying to do, however, I believe the INDIRECT formula is what you need.

This way you can have the TYPE in cell A7="EMT" and then your formula could be something along the lines of
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE,INDIRECT(A7))

 

[derrack]

It all ready references the Type column how I would like. What I need is for the code to function more like.

Instead of
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE),MISC,THHN,XHHW,GRC,EMT,PVC)

This
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE)D29)

And cell D29 would have

,MISC,THHN,XHHW,GRC,EMT,PVC

Then I could add to the one cell a new named range and it would update the 1000+ rows with this formula.

 

[derrack]

What I really want is for
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE),MISC,THHN,XHHW,GRC,EMT,PVC)

To be
=CHOOSE(VLOOKUP(B6,TABLE,2,FALSE)TYPE)

Because the name type already is a list of the named ranges
,MISC,THHN,XHHW,GRC,EMT,PVC