A Seriously MAJOR defect in excel

[sonofearth]

Hello everybody

It's simple .... there is a defect in excel which is ... when you create a workbook in excel 2010, then put some features on it like the data validation and the sheet protection ..... then you upload that excel workbook on a shared place like a sharepoint site or a any shared space .... and when you give access to other people to work on that sheet ....if they are using an earlier version of excel ....like excel 2003 the protection and validation features will be lost and they will be able to manipulate and change whatever they want to change ...... no Protection , No Validation ...... nothing ... they will be able to mess with your sheet as much as they want , not as much as you allowed them when you created the validation and protection features, because they have the earlier versions of excel that doesn't support the new features ........... ANYONE HAVE SEEN THAT BEFORE ?? ANYONE CAN HELP WITH THAT ?

[abousetta]

I have not, but everyone in the office uses 2010. When you come to save your workbook as 2003 (*.xls) does it give you a warning that this validation will be disabled? Could you upload a dummy copy of your workbook so someone with 2003 can have a look at it. You can remove your data just let us know where the validation and protection is.

[sonofearth]

Oh sure , defenitly I'll upload a copy ... and I don't want to save it in excel 2003 format , because those features are not available in 2003 ..... So here you go

[abousetta]

It's as I expected. These validation rules are not available prior to Excel 2007. Therefore when your colleagues are opening the file using Excel 2003 (with the Excel viewer), their version of Excel doesn't recognize the commands and therefore ignores them. You could write a small validation code in vba that would do the same thing but then macros would have to be activated before using the workbook (which may or not be allowed in your work environment).

[npamcpp]

You are using data validation that refers to ranges on other worksheets. That is a new feature in 2010 and will not work in earlier versions. Hence the data validation will break.

This error is marked as "Significant loss in functionality" when you save the file in 2003 format. If your workbook will be opened in earlier versions than Excel 2010, then you'd better design it in compatibility mode, using the tools and techniques that are available in those earlier versions.

Excel has quite decent downwards compatibility, but you cannot expect earlier versions to know and implement the features of later versions.

The one rule of thumb: If several versions of Excel are involved, always develop in the earliest version in the mix.

regards,

[sonofearth]

Thank you so much Guys , so i think the only solution for that is to write a validation code or to redesign the sheet using the earlier version of excel ... but ... don't you agree that this is a serious security defect ?..... I mean you can manipulate files that you're not allowed to manipulate .... How could this be ??

[Cutter]

Not sure but I think you can use Data Validation referring to other sheets in older versions if the ranges are named.

And, its only a "serious security defect" if you ignore the warnings.

[abousetta]

I have learned to accept that secure is a relative term. "Security" is often breached by minor things. I can't go into the details of how unsecure Excel is but for most users, the available security is enough. The important rule of thumb is to test your product on the end-user's system, if available, and if not try to simulate it as best as you can. You are better off creating the Workbook in Excel 2003 if that's what your ebd users will be using.

Good luck.

abousetta

[royUK]

The protection should not be affected when opening with an earlier version, but Excel protection is simple to break anyway.

It's standard practice when developing for multiple versions that you develop in the earliest version, so I would always use named Ranges for Data Validation lists.