Excel 2007 : Compatability Issue

When i do conditional formatting using a specific farmula and save my document, it gives me a warning and after I close the file and then reopen it. Conditional Shading is allover the column where i applied conditional formatting. I have to go and again apply the conditional formatting.

CONDITIONAL FORMATTING > MANAGE RULES > EDIT RULE > OK > APPLY .

Please see the attachment for the warning it gives me when i save the file. I am trying to save in default file format i.e. excel workbook.


Compatibility Report for FALL 08 DATA FINAL.xlsx
Run on 5/19/2009 17:33

The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.

Significant loss of functionality # of occurrences

Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting. 142
'AUG_SEPT'!G3:J28
'SEPT_OCT'!F3:J39
'SEPT_OCT'!F1:J2
'OCT_NOV'!F3:J120
'OCT_NOV'!F1:J2
'NOV_DEC'!F3:J100
'NOV_DEC'!F1:J2

One or more cells in this workbook contain a conditional formatting type that is not supported in earlier versions of Excel, such as data bars, color scales, or icon sets. 3
'SEPT_OCT'!A2:J2
'OCT_NOV'!A2:J2
'NOV_DEC'!A2:J2


Minor loss of fidelity

Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. 25

There is no attachment.

If you save a workbook in the earlier (xls) format rather than xlsx or xlsm, the conditional formatting (and other 2007-specific features) not supported by earlier versions are going away. If you don't want to lose it, save in one of the 2007 native formats.

xlsx for workbooks without macros, xslm for workbooks with.

Same problem i saved in native format.

I have both excel 03 and 07 installed on my PC. I saved the file in native 07 format several times. Still i have to apply the conditional formatting each time.

So when that message comes up, untick the box that says Continue checking compatability for this file

Problem is not the message. The problem is...

When you open the file the sheet named AUG_SEPT has a column "PURCHASES" . Under this column you will see names of the shops from where the purchases are done shaded in red dot boxes. These boxes are not suppose to be shaded unless each name of the shops in the purchases column misses a keyword that are listed in VENDORS sheet. These boxes are conditionally shaded with a formula. This conditional formatting doesn't work the first time when one opens the saved document and it throws shades all over the column. But when you reapply the conditional shading as i mentioned before every thing works fine. So is there any way that we dont have to reapply conditional formatting each time we open the document. I believe it has something to do with compatibility.

OR Do you know any macro solution !!!

I highly appreciate your help in this matter.

Ah, sorry, slow learner.

Your CF formula is an array formula, and I'm not sure it returns correct results. I'm on my way out the door for the day, but maybe one of my compadres will look at this. If not, I'll look back this evening.

Easy solution would be to use a helper column. That would contain the array formula and then the CF could be a simple test against the helper cell being true/false

Array formulas are special Excel formulas that need to be confirmed with a specific set of keys.. ie. CTRL+SHIFT+ENTER. See this article: Array Formulas

That said, in K3, enter formula:

Please Login or Register  to view this content.

and confirm it with CTRL+SHIFT+ENTER. You should see { } brackets appear around it. Then copy the formula down...

Now edit your Conditional format formula to formula is: =K3

That should do it.

You can hide column K if you want.

P.S. I am not sure why that doesn't work as you had it before.. I will try in 2003 and see.. maybe 2007 CF doesn't like array formulas?

Yeah, its XL2007, it seems... the same thing in 2003 works fine...

bilalbajwa,

Here is an alternate conditional formatting formula that seems to work in XL2007...

No need for the K column....

Try editing your conditional formatting formula to:

Please Login or Register  to view this content.

and if you don't want blank cells highlighted...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is an array formula as mentioned earlier and must be confirmed with CSE key combo to work.

The SEARCH() function searches for a individual value (at first, VENDORS!$A$2) to see if they are within the text string in (C3).. if yes, the SEARCH() function returns a position number (ie character location) of the first character in the matching string. If it doesn't find a match, an error (#N/A) is returned... the ISNUMBER() function checks to see if a position number is returned and returns TRUE if yes... (note: the actual position does not matter..just that a position has been located). The OR() function allows, along with the CSE confirmation, allows you to search multiple possibilities (i.e range VENDORS!$A$2:$E$75) and if any return a position number, the overall result is TRUE... Since you want to know if the item is NOT found, then the NOT() function is used to give the opposing boolean result of the SEARCH result... So FALSE becomes TRUE and is therefore highlighted by the CF.

The INDIRECT() wrap around the VENDORS!$A$2:$E$75 is needed to indirectly refer to the range since Excel does not allow you to use ranges in other sheets for Conditional Formatting.. The INDIRECT() is a workaround trick. You can also use named ranges as a workaround.

Please Login or Register  to view this content.

In this formula, this part is the same as for the previous formula. ISNUMBER(SEARCH(INDIRECT("VENDORS!$A$2:$E$75"),C3)

The SUMPRODUCT.. basically converts this condition to an array of results.. (so an alternative to the OR()... the -- converts the results of TRUEs and FALSEs to 1's and 0's and so the Sumproduct can now count the number of matches... if the the result is 0, then no matches are found, therefore highlight the cell...

This part is added: C3<>"" within an AND() function, so that the blanks are not taken as mismatches and are instead not coloured.

As for learning the stuff and books to refer you too.. that is quite difficult to do.. as many here will tell you.. these are things you learn on an as needed basis and sticking to the forums here and recommended sites... books and any particular site would never cover all the things I've learned in this and other forums I've frequented in just the last few years.

Having said that have a look here for many different links to look into.

http://www.excelforum.com/excel-gene...additions.html

Hope this all helps.