Conditional Formats being Stripped for no reason?

[liquidmettle]

Hello Everyone,

I have a problem I am at a loss to understand. I created a dashboard to track how my company is performing in various goals and areas.

This was made using Excel 2010. The dashboard and all files work great when transferred or opened on other computers as long as they also have Excel 2010.

When opened with Excel 2007 some sheets have lost specific conditional formatting rules.

The CF only disappears on computers with Excel 2007. I've tested it on two comps with 2007 and 3 comps with 2010. The the files work great and as intended on all computers with 2010 but not in 2007.

Each dashboard has 5 worksheets that make use of CF rules to display red if the goal is not being met, yellow if its close, and green if its met or exceeded.

When opened in Excel 2007, 3 of 5 CF rules are removed from 4 of the sheets (in all cells that use them) and at least two rules are removed from the 5th sheet.

I thought that Excel 2007 and 2010 share the same file extension that there should be no issues of this nature and I am at a loss to explain exactly why it is occurring.

If anyone can give me an explanation and possible solutions that would be great! Once 100% complete, this dashboard will be available on a common drive and must be able to work with Excel 2007 and 2010 as both are in use on various computers here.

Thanks,

-LM

P.s. Excel 2003 is not being considered or used and no files are in this format. Saving to 2003 causes too many issues with the dashboard files.

 

[shg]

Have you tried applying the formatting in 2007 and opening in 2010?

 

[RoryA]

What are the actual rules that disappear on 2007?

 

[liquidmettle]

SHG,

Unfortunately the main computer I work on uses 2010 and doing more than quick testing on 2007 isn't very easy. I will note that after I posted this I did a test and a file updated in 2007 then later opened in 2010 has the proper CF rules again (2007 just appears to refuse to show them-as in they aren't even in the CF manage rules menu).

In addition, applying the formatting rules, at minimum, would span 390 non-contiguous cells per program workbook (Although I could just edit one and save it a bunch of times then edit the contents to match the individual programs but quite frankly that's too much superfluous work).

Rory,

The rules that disappear from the 4 sheets (since they are the same rules) are as follows:

Format only cells that contain category:

1. If Cell Value is Less Than ='Data Entry'!$L$14

Then format color cell red.

2. If Cell Value is Greater than or Equal To ='Data Entry'!$i$14

Then format color cell Green.

3. If Cell Value is Greater than or Equal To ='Data Entry'!$L$14

Then format cell color yellow.


They are in that order in the Rules manager so that green will superceed yellow if the conditions are met (since yellow is the inbetween failure and success color).


The rules that disappear on the primary dashboard sheet are simpler:

Format Only Cells that Contain Category:

1. If Cell Value contains ="-"

Then format cell color red.

2. If Cell Value contains ="+"

Then format cell color green.


There is a third rule that is exactly the same for yellow but the yellow rule does not disappear. Yellow uses a ~ as the condition met.

The formulas in these cells will return the -, +, or ~ based on the data entered.


There are some other format rules, like shade cell and text gray if they are empty etc that also seem to remain when opened in 2007.

The design layout and presentation of the dashboard was created to be user-friendly and simple (although the nuts and bolts are complex) for people of all levels of Excel familiarity (since some who at least look at this will be novice/somewhat computer illiterate) to use. While I may have to settle for a disclaimer that it will only display right in 2010, I'd prefer to have it work in both version of excel if possible.

Thanks to both of you for your fast initial replies.

 

[RoryA]

I suspect the reason for the first three is the direct reference to a different sheet, but I can't see any reason that the latter two would not work in 2007

 

[liquidmettle]

And that Rory is the source of my confusion : p.

Do different sheet references of the same workbook often cause these kinds of issues?

 

[shg]

The ability to directly reference other worksheets in CF was introduced in Excel 2010. It isn't supported in prior versions.

You could create a name with workbook scope (or scoped to the worksheet where the validation is used) to refer to those cells.

 

[liquidmettle]

Hello SHG,

To clarify for myself, are you saying just to apply named ranges to the cells otherwise referenced in the formulas above and then in the CF menu do something like:

If Cell Value is greater than or equal to NAMEDRANGE

Would that, firstly, work like that? And do you think it would then be compatible in 2007?

Thanks for the additional feedback I look forward to your response.

 

[shg]

What happened when you tried it?