Data Validation in Excel 2007

[markjl]

Hello

I created a sheet in Excel 2013 using data validation against values in another sheet. This sheet didn't work in Excel 2007.

I found the info that said to use a named range. That's ok data validation works again, but can only display 7 values.

Can you think of any way round this 7 value limit in Excel 2007? Thanks

[TMS]

AFAIAA, there is no 7 entry limit. You would need to define it as a Dynamic Named Range rather than a simple static one. Please post a sample workbook for a more specific/definitive reply.


Regards, TMS

[markjl]

Hello Trevor,

Thanks for your quick response, much appreciated.

In fact (it seems) a standard name range does work, I just needed to extend the range of the name.
What doesn't work in Excel 2007 is referring to data validation values on another sheet directly as in =Sheet2!$A$1:$A$10

Wondering if there is a difference if I create the file in 2007 or in 2013 first and then save as v2007.
In fact in Excel 2013 there is no 'save as v2007' or 'save as v2010', but as v 97-2003

File attached.

Regards Mark

[TMS]

I prefer this:

Please Login or Register  to view this content.

because INDEX isn't volatile, whereas OFFSET is.

It is also easier to determine the cells to use if you don't have blank rows at the top. Hence the +2 adjustment above.

In fact in Excel 2013 there is no 'save as v2007' or 'save as v2010', but as v 97-2003

That is because the format of the files changed with 2007. So, the 2007, 2010 and 2013 format is the same ... although there are different functions available in 2010 and 2013.


Thanks for the rep.


Regards, TMS