schuyler
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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
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).
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
dave
ASKER
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
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
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