+ Reply to Thread
Results 1 to 19 of 19

Excel 2007 : Compatability Issue

  1. #1
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Thumbs up 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

    Last edited by bilalbajwa; 05-20-2009 at 08:25 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Compatability Issue

    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.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Compatability Issue

    Please tell me whats the native format?

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Compatability Issue

    xlsx for workbooks without macros, xslm for workbooks with.

  5. #5
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Compatability Issue

    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.
    Last edited by bilalbajwa; 05-19-2009 at 06:33 PM.

  6. #6
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Compatability Issue

    hERE IS THE ATTACH FILE:
    Attached Files Attached Files

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Compatability Issue

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

  8. #8
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Compatability Issue

    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.
    Last edited by bilalbajwa; 05-19-2009 at 08:16 PM.

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Compatability Issue

    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.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,081

    Re: Compatability Issue

    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
    Cheers
    Andy
    www.andypope.info

  11. #11
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Question Re: Compatability Issue

    Thanks for replying

    Please help me a little bit more i am a new bee in this stuff. Your suggestion makes 10% sense to me; therefore, i am still lost. An example is highly appreciated.

    Any other solution is welcomed as well. Please also tell me whats an Array Formula?

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Compatability Issue

    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?
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Compatability Issue

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

  14. #14
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Smile Re: Compatability Issue

    So Excel 07 has a bug or something? As u said the same thing works in Excel 03!


    I cannot use XL03 as it doesn't support most of the graphical things that i did in XL03. Am i right? or i can do exactly the same things in XL03.


    I will try the K column thingy.
    I will post if any thing come up. IF it worked i will mark the post as SOLVED.

    Thanks
    You guys are very helpful

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Compatability Issue

    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.

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Compatability Issue

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

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Thumbs up Re: Compatability Issue

    Thanks A LOT

    Can you please explain both formula's step by step. I am also learning this stuff. I highly appreciate your help.

    One question: How long you are doing this stuff?

    As i asked earlier please refer me to some website or book that would equip me with enough knowledge so that i figure out the solutions to the problems myself.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Compatability Issue

    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.

  19. #19
    Registered User
    Join Date
    05-18-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Thumbs up Re: Compatability Issue

    Thanks a lot everybody.

    I dont feel like even a toddler in this stuff. Anyways i am marking this thread as SOLVED and will return with a new post if any other issues comes up.



    This forum is AWSOME.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1