Dynamic Named Range

schuyler
schuyler used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
Absolutely.  Not only are dynamic lists great for data validation, but also for dynamic charts, etc.  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.  However, without a table (the table list object doesn't exist in Excel 2003), the solution is not that complicated, and there is perhaps added flexibility in understanding the following, without the use of tables, for dynamic assessments of spreadsheet data.

If your list is displayed vertically, and is numeric, then you can create a name - e.g., say the range starts in A1 and goes down the A column:

=$A$1:INDEX($A:$A,MATCH(99^99,$A:$A))

if its a text-based range, then use:

=$A$1:INDEX($A:$A,MATCH(rept("z",20),$A:$A))

Then, just assign your data validation list to the range name you created based on this formula (or just add this formula to your data validation list, though I think using named ranges easier to manage) and now its a dynamic data validation list.

Here's an article from Contextures on the subject (tips/quirks on data validation including dynamic ranges), which shows another approach (if you follow the links to dynamic ranges) using the OFFSET function:
http://www.contextures.com/xldataval08.html

I prefer INDEX/MATCH approach, but that's because that's the way I originally learned how to create dynamic ranges, and it has been easier for me to remember.  Also INDEX/MATCH approach is not volatile, while OFFSET is, so INDEX/MATCH should be more efficient/less resource intensive.

Also, if you want your dynamic data validation range to also be sorted (an option) and unique/distinct list, you might enjoy my article which provides DynamicDV! an approach that delivers this feature as an add-in or can be coded directly in your workbook if you're averse to add-ins:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

If you find it helpful, please vote YES.

See attached.

Dave
dynamicRange-DV-List-r1.xls
Jan Karel PieterseExcel and VBA Expert

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Jan Karel PieterseExcel and VBA Expert

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Jan Karel PieterseExcel and VBA Expert

Commented:
In 2003 they are called Lists (Data menu).

Author

Commented:
Got side tracked...I won't forget about this...I should be able to test tomorrow...thanks so much everyone.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Get it working?

dave

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial