PDA

View Full Version : Solved: Dynamic Ranges not working with Data Validation



JimS
10-24-2010, 08:18 AM
I have several Named Ranges that are referenced when using the Indirect formula "(=INDIRECT(A2)" as the Data Validation source. This works fine.

When I converted the Named Ranges to Dynamic Ranges "(=OFFSET(Data!$M$2,,,COUNTA(Data!$M:$M)-1)" to accommodate for new entries in each list the Indirect Data Validation no longer works. It will not display the list in the range.

Can Dynamic Ranges be referenced by Data Validation?

Thanks for any help…

JimS

mdmackillop
10-24-2010, 08:52 AM
It looks like you are missing the final column dimension. Try

=OFFSET(Data!$M$2,,,COUNTA(Data!$M:$M)-1,1)

JimS
10-24-2010, 09:09 AM
Tried that but it still does not work.

I've attached an example file.

JimS

Bob Phillips
10-24-2010, 09:43 AM
TRy this

mdmackillop
10-24-2010, 09:57 AM
Have a read of this at the bottom of the page
Using Dynamic Lists (http://www.contextures.com/xlDataVal02.html)

JimS
10-24-2010, 12:59 PM
Thank you - both of you...

JimS