Dynamic Named Range inside a Data Validation list ?

[Richard]

Is it possible to use a 'Dynamic Named Range' in the Data Validation
list ?
When I tried, the Data Validation List did NOT show any entries... ?!
(it remained empty, enven though the Dynamic List was working right!

Details on 'Dynamic Named Ranges' can be found here:
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

If this is not possible, is there any ways/workarounds to create a Data
Validation List, based on a Dynamic Named Range ?

TIA !

R.

 

[Guest]

Hi Richard,

This should work if your named ranged is "dynamic" as in created using a
formula. The required criteria for the DV dialog is "List", with
=MyDynamicRangeName entry in the RefEdit box.

Regards,
GS

 

[davesexcel]

http://www.beyondtechnology.com/geeks007.shtml

I came accross this site, it is about dynamic ranges, and may be of use
to you

 

[Guest]

Richard, I failed to mention that the defined name for your dynamic range
must be workbook-level.

Regards,
GS

 

[Randy Harmelink]

I was just working on a workbook where I had to create a dynamic named
range for a data validation list. I defined a name of "TableList" as:

=OFFSET('Table Definitions'!$B$2,1,0,ROWS('Table
Definitions'!$B$2:$B$52)-2,1)

B2 is my header line of the table and B52 is a physical "end of table"
line I have added to my table. Anytime I insert something between the
header line and the "end of table" line, the entry will be added to my
named range and used in my validation list.

I'm basically using the B column as my definition of a drop-down box.
Once the item is chosen, all the other parameters can be retrieved from
other columns of my table by doing MATCH()/OFFSET() or VLOOKUP()
functions based on that column.

 

[Debra Dalgleish]

You can use a dynamic range in the same workbook. If the list is in
another workbook, there are instructions here:

http://www.contextures.com/xlDataVal05.html