2010-2007 Data Validation Lists

[nunyrdambizness]

Ok, I have experienced the data validation lists created in 2010 not displaying in 2007 issue. I have also found some information as to how to fix it; use named ranges.

Is there any other fix besides named ranges? I ask only because my data validation list is built off of dynamic data, therefore, I can't name the ranges.

Thanks.

EDIT: Ok, I tried saving the file as an Excel 97-2003 xls and that didn't work either. This is really dumb that Microsoft won't allow something like this.

[benishiryo]

hi nunyrdambizness, a named range can be dynamic using the OFFSET formula. u can also use a table for your data validation list so it becomes dynamic.

[Marcol]

1/. Avoid OFFSET() when defining dynamic named ranges, it's a volatile function and can severely impact on the efficiency of larger sheets.
Better to use INDEX() and MATCH()

2/. COUNTA() will return unexpected results if the list is formula derived and returns blanks.
If the list is continuous and ends with formulae returning blanks, use COUNTIF("your range","<>""")
If it isn't use INDEX() and MATCH()