Drop Down Boxes will work in 2010 excel and then will not work in 2007 excel

[superdutyfan]

Hello everyone, sorry if this is the wrong spot form my question,

Our company paperwork was created with 2007 office excel and has worked pretty good until new people with 2010 excel use it works fine for them if they forward the file after using 2010 excel to someone using 2007 excel some macros and drop down boxes have disappeared. The only time I come across this problem is when 2010 excel is used.

I can't update to 2010 because I run Xp with service pac 2 and I need Xp for other software I am using is not supported by vista or windows 7.

Thanks For Any Help In Advance SDF

[Roger Govier]

Hi

Can you upload a sample workbook showing the problem?

[superdutyfan]

I tried but the file exceeds size limits its 1.16 mb can i send else where

[Roger Govier]

Hi

You can send it direct to me if you want.
roger at technology4u dot co dot uk

Do the obvious with the at and dots

[Ken Puls]

Another option is to delete some of the irrelevant data/sheets, or possibly to put it into a zip file. Either should help compress it down in size a bit.

(Roger, thanks for your generosity in the email route though!)

[Roger Govier]

Hi

I can confirm the behaviour you describe.
There are a number of Named ranges in your workbook with #REF errors that need sorting out, but that cannot explain why data validation is totally missing from cells when opened in 2007, but is present when opened in 2010.

I have managed to get your validation to work in both versions for the cells you highlighted in blue.
In 2010 I created a named range called Personnel referring to ='Job Information'!$E$17:$E$20
Then the DV in cells in Sheet Tour Day cells U19:Z22 have the DV of List = Personnel
instead of
List = ='Job Information'!E17:E20

Similarly I made a named range called SerialNo
='Well Inventory & Daily Usage'!$A$22:$A$78
which I applied as DV in Cell A16 of sheet Mtr. Eval. as List = SerialNo
instead of List = ='Well Inventory & Daily Usage'!A22:A115

Saving the Workbook, then opening in 2007 shows the same DV in both cases.

It may be that it is because you have merged cells where you are applying the DV that caused the problem, but, it seems that if you apply a named range to these merged cells as opposed to direct sheet location, then it works fine in both versions.

I have mailed a copy of your workbook directly to you.