Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    I recently upgraded to Office 2010. I have been successfully using the following formula for conditional formatting =OFFSET(course:Position,MATCH($E6,Position,0)-1,MATCH(BW$6,course,0))="E", prior to upgrading.

    However, when I open the spreadsheet in 2010 all the conditional formatting falls off and if I try to redo it using the formula above, I get the following error "You may not use reference operators (such as unions, intersections and ranges) or array constants for conditional formatting"

    Any one had any problems like this or know of anyway to resolve the problem?

    Regards

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post a sample with the CF as you originally had it?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    *Actually, I have just been involved in a similar discussion re Data Validation in 2010. The reference operator (the colon) seems to be the issue. You can use it in a named range and then refer to that in a CF or DV formula, but you can't use it directly. No idea why they have decided to change that suddenly.*
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    rory,

    Thanks for the replies. I'm not really sure what you mean. I understood that the "course:Position" were already named ranges, so I don't know how to make a combined name range

    Any examples of how I could change the existing formula.

    Any other thoughts would be appreciated

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Why are you trying to offset from a multi-cell range when you are doing a straight comparison to one value?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    rory,

    This formula was developed with the assistance of the Lounge some time age and worked extremely well. It matches two cells(course and position) with the value, with can be either "E" or "D" and the cell is conditionally formated to suit.

    If you can suggest a better way (that will work with 2010) to achieve the same outcome, that would be greatly appreciated.

    Regards

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can probably get round it by defining a name equal to course:Position and using that in the formula. Would need to see a sample to suggest other alternatives.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Rory

    Thanks. Will try to sort out a cut down version and attach

    Regards

Posting Permissions

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