Dynamic named ranges in excel

[Luvsql]

I have a bunch of data that I will be using to update a list box on another sheet. The names that I need to create are many ie I need to name a bunch of items by their category and this then needs to be the name, so that when I have a list box for category, the next cell will then list all the items within that category.

Is this possible?

 

[AnotherHiggins]

Are you talking about something like this:
[tab]thread68-1469736
?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Luvsql]

What I need to do is from a list of 1000 items and 30 categories, I need Excel to create the Named Ranges for me (using the category ID from the sheet) so that I don't have to manually create each one with each range of items. The list part I can do, but want to avoid manually setting up all these ranges.

 

[SkipVought]

Hi,

Please explain exactly what you mean.

And let me explain what I mean by named ranges with a simple example...
[tt]
FruitColor Fruit
Red Cherry
Red Raspberry
Red Strawberry
Yellow Lemon
Yellow Banana
[/tt]
I have 2 named ranges: FruitColor and Fruit.

I would either use Insert > Name > Create > Create names in TOP row or Insert > Name > Define... using the OFFSET function.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Luvsql]

Here's what my data looks like:

Category Item
Arcs Description1
Arcs Description2
Ballasts Description3
Ballasts Description4
Ballasts Description5
Cable Description6
Cable Description7

What I need excel to do is create the named ranges: Arcs(B2:B3), Ballasts (B4:B6), Cable(B7:B8). I have about 10-15 items per category and about 30 categories so to set these up will take forever. Plus, if I need to update the item list later, I have to redo them all.

 

[SkipVought]

Tha't not really how named ranges work.

Ballasts is a data element, not a range name.

Here's the range for Ballasts (formula in column Z row 2)...
[tt]
Y Z
0 0
Ballasts =OFFSET($A$1, Match(Y2,Category,0),0,COUNTA(Category,Y2),1)
[/tt]
So then your SECOND description for ballast would be...
[tt]
Y Z
2
Ballasts =INDEX(OFFSET($A$1, Match(Y2,Category,0),0,COUNTA(Category,Y2),1),Z1,1)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

If you're looking at creating dozens of Named Ranges and wanting Excel to do it for you, I'd use Skip's suggestion of using the "Create names in TOP row" functionality. First, rearrange your date to look like:
[tt]
Arcs Ballasts Cable
Description1 Description3 Description6
Description2 Description4 Description7
Description5[/tt]

If you select ALL CELLS before telling Excel to create the Named Ranges, it will default the references to include entire columns. That means that if you add an item to a category later on, the Named Range will act Dynamic.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

I never like range with empty elements.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Luvsql]

I think just the time it's going to take to arrange my cells that way will take me as long as manually creating each named range. I have 1500 rows, with 30 separate categories (would become 30 across).

 

[SkipVought]

That's a NIGHTMARE, which is yet another reason I would argue against the individual naming approch. That is a Non-Normalized data structure and certainly not a best and accepted proctice, IMNSHO.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Skip: I can understand that. Excel 2007 would only refer to populated cells, ignoring all the null cells at the bottom of any particular column. But now that you mention it, I'm thinking that 2003 behaved differently, right?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Yes, But even at that 2007 feature, its like adding a table for each new month's data. That is not a proper data structure that works well with other data analysis and reporting methods.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I solved a similar issue by using the following approach:

Pivot table with row fields for Category and Items and count of category as the data field.

Field Settings for category:
Subtotals - none
Layout - Outline View

Now select the entire pivot table and convert to values (copy, pastespecial)

Select the item column (not the entire column - just within the table)
Edit,goto special blanks (all the blank cells should now be selected)
={left arrow} Ctrl-Enter (The selected cells will now contain the Category values

Select the Category column,
Edit,GoTo, Special, Constants
Edit Insert Rows

Your data is in a format whereby you can step through it using Insert, Name, Create, TopRow

To step through use Ctrl-Down to get to the category headers and Ctrl-Shift-Down to extend to include the Items for that Category.

I created a crude macro for the stepping through.

I was actually watching this thread for a better solution but..




Gavin