Link to home
Start Free TrialLog in
Avatar of schuyler
schuylerFlag for United States of America

asked on

Dynamic Named Range

I need to be able to build the list for my named range dynamically.  It will start with a list of 10 values but I would like to be to define new values as a am proceeding with my work.  Is is possible to be able to add to the dropdown list of values dynamically?
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jan Karel Pieterse
An alternative to using dynamic range names is to first convert your table to a list (Excel 2003) or Table (Excel 2007/2010). Then define "static" range names, which automatically will grow and shrink with the list/table.
That's what I referred to in my opening post, re: You could create a table and a named range based on a column in that table.  Then when new values are added, the range would dynamically adjust as well.

To expand on this a bit more, if you are interested in using tables...

I suppose you could reference the table range directly (with structured references) as opposed to creating a range name based on the table range.

Here's a bit more on structured referencing of excel tables:
http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

Dave
I guess I didn't make myself clear; I wanted to explain that Excel 2003 DOES have a list object, which also allows you to create dynamic range names while using direct cell references in the range names.

NB: I just tried and it seems you cannot use structured table references in Data validation.
I was operating under the assumption that tables weren't in Excel 2003...  Hmmm, my bad on that one.  Apologies.

Structured table references - apparently you do have to create a range name using the structured reference - good to know.

Thanks so much for clarifying.  Rather than "I suppose" I should have tested.  double bad!

Cheers,

Dave
In 2003 they are called Lists (Data menu).
Avatar of schuyler

ASKER

Got side tracked...I won't forget about this...I should be able to test tomorrow...thanks so much everyone.
Get it working?

dave
Thanks so much...I will review th eother links you provided, I'll need to be able to sort this list as new data is enter...

Appreciate everyone who responded.

Schuyler
Ok try the DynamicSV! Addin from the article I posted

It's a quick read and the add-in will ensure your drop down lists are sorted

If you need any help or have questions don't hesitate to ask.
Dave