Data Validation does not refer to list in another sheet.

[onenessboy]

Hi Experts,

I have column of data on sheet 1 where i am trying to refer data list which is in next sheet (sheet2)..

data-->allow--->list then i am trying to select source from sheet 2.. its not allowing me either...why...


It allowed in my 2010 version..but not in 2007 version now...

[Fotis1991]

Hi

Give a name to your list in Sheet!2 and use it in Data Validation in your other sheet...

[npamcpp]

A reference for data validation (or conditional formatting) cannot point to a range on another sheet in versions before Excel 2010. That is a new feature in Excel 2010 and will not work in earlier versions. For those, you need to define a range name with the name manager (Formulas ribbon > Name manager) and use that name in the data validation source. This will work in all current versions of Excel.

Remember: if you are building a spreadsheet that will be used in different versions of Excel, always build in the oldest version that will be used. That way, you can ensure that it will work in all versions used. Upwards compatibility is never an issue. But functionality that is new in Excel 2010 (like pointing a data validation range to a different sheet) will break if the file is opened in earlier versions of Excel.