Data Validation Bug in Excel 2010?

[Duvnjak]

Hello,

I am having some issues testing an Excel 2003 template in Excel 2010. My main issue is with Data Validation lists.

When I export an .XLT worksheet (saved in 97/2003 format) that contains a number of Data Validation lists and then open it in Excel 2010, some of my validation lists magically disappear. I'm not exactly sure why. When building data validation lists, I am using named ranges that reside on the same worksheet. I have cleared and rebuilt the data validation lists with varying results, but the end result is always the same - I am missing one or more validation lists.

Has anyone else encountered anything similar?

Thanks in advance.

 

[RoryA]

Can you give an example of one that disappears and what precisely is entered into the source box in 2003?

 

[Duvnjak]

I have a spreadsheet used for calculating labour with four sections:

FT
PT
Key
Mgmt

In each section I have a column called PayBase. The user is expected to select a Pay Base (column B) for each employee added (this will be either salary or hourly which is also what my data validation list is based on).

Originally, I had placed the following values in P1 and P2 (which is hidden) respectively:

Salary
Hourly

and named this range PayBase

For some reason, I get the lists showing up in FT but nothing in PT, Key or Mgmt

However, I just figured out that if duplicated the above range 4 times and renamed each one to PayBaseFT, PayBasePT, PayBaseKey and PayBaseMgmt, everything works out beautifully.

It just makes no sense to me why I should have to replicate the original range four times in order for this to work properly.