+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting Excel 2007 to 2010 errors

  1. 11-17-2012, 01:21 PM #1
    ThomDall
    ThomDall is offline
    Registered User
    Join Date
    11-17-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional Formatting Excel 2007 to 2010 errors

    Hi, I was trying to make a conditional formatted attendance sheet. The idea being to have a visual warning if students have missed a certain number of classes so far. It worked fine in excel 2007 but when someone tried to open it in excel 2010, it all blew up.

    The formatting formula which worked in excel 2007 is

    =COUNTIF($E6:INDIRECT($A6),"-")>2

    where A6 stores the value "M6".

    When I tried the same thing in excel 2010 it said: "You may not use reference operators (such as unions, intersections, and ranges) or array functions for Conditional Formatting criteria"


    To better explain what I'm trying to do I've included a copy of the spreadsheet which worked in excel 2007


    Any know how I could change this to work in excel 2010?

    Thank you!

    Attendance.xlsx
    Reply With QuoteRegister To Reply
  2. 11-17-2012, 01:39 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Conditional Formatting Excel 2007 to 2010 errors

    I only have 2007, but the only time i have seen that error msg is when I have tried to reeference something thru CF on another sheet?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 11-17-2012, 01:51 PM #3
    ThomDall
    ThomDall is offline
    Registered User
    Join Date
    11-17-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Formatting Excel 2007 to 2010 errors

    It's all on one sheet, no reference is made to another sheet
    Reply With QuoteRegister To Reply
  4. 11-17-2012, 02:34 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Conditional Formatting Excel 2007 to 2010 errors

    ok try this...

    1st, remove all the "-" from the columns you have not yet reached (date-wise)
    2nd remove all of the CF you have now
    3rd highlight the range, go into CF. new rule, use formula and copy/paste this...
    =AND(E6="-",NOT(ISBLANK(E6)))

    you may even be able to remove the helper column now too

    Edit: oops forgot to test for more than 2 "-", change it ti this,,,

    =AND(COUNTIF($E6:$AH6,"-")>2,E6="-")
    Last edited by FDibbins; 11-17-2012 at 04:40 PM.
    Reply With QuoteRegister To Reply
  5. 11-17-2012, 03:08 PM #5
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Conditional Formatting Excel 2007 to 2010 errors

    As an alternate, you can also try the below in conditional formatting in 2010:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Hope this works...
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.
    Reply With QuoteRegister To Reply
  6. 11-17-2012, 03:24 PM #6
    ThomDall
    ThomDall is offline
    Registered User
    Join Date
    11-17-2012
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional Formatting Excel 2007 to 2010 errors

    kbkumar you are my hero. Works great!
    Reply With QuoteRegister To Reply
  7. 11-17-2012, 03:29 PM #7
    kbkumar
    kbkumar is offline
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Conditional Formatting Excel 2007 to 2010 errors

    Just realised a small glithch. Use this instead:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1