Data Validation pull down list missing after file being passed around

[Klepster]

Hi, I've searched around the forum and couldn't quite find a solution that would match my issue. Sorry if I end up re-asking a question though.

I've made a few pull down menus, hid the sheet with the actual data, leaving only the pull down menus available, and locked the sheets to provide different levels of accesses. However, when the file gets passed around to get information filled in, the pull down list would no longer exist, even though the value inside the cell is still inputted as the data validation formula.

Some suggests that it's because of the different versions of 2007 that is being used..., but I don't know how version differences would kill the data validation.

I'm using MS Excel 2007.

 

[jkpieterse]

Excel 2007 does not allow a data validation formula that points to a different worksheet. If you created the file in 2010 and pass it around, Excel 2007 will ignore the Data validation and when saved, the DV is removed.
Since you say you are using 2007, I am not sure what is causing this.

 

[Klepster]

Hi Jan,

Thanks for the reply, although, I was able to make the DV, with 2007, that points to a different worksheet... and that it doesn't remove the DV after saving either.

 

[jkpieterse]

If I open such a workbook in 2003 and subsequently save it the DV is broken.

 

[Klepster]

would it be possible if i define the list with a name, and so that would not remove the pull down list because the pull down list would be from the same sheet, but the cross sheets would be only using formulas of the defined names?

 

[jkpieterse]

Yes, that should prevent the problem from happening.