+ Reply to Thread
Results 1 to 2 of 2

conditional format based on sum of value assigned to partial text field

  1. #1
    Registered User
    Join Date
    12-19-2010
    Location
    DC
    MS-Off Ver
    Excel 2003/2007
    Posts
    1

    Exclamation conditional format based on sum of value assigned to partial text field

    Hello all, please help!

    I would like to conditionally format a cell, based on the sum of values assigned to partial text fields in the same row.

    confused? me too.

    i have attached the sheet i am working on. basically, i want to highlight the name in red using conditional formatting, when the number of hours reached in that sun-sat row equals more than 40 hours when the fields are filled with a code. The trick is, I need to assign # of hours to correspond with the location/shift assignment in the cells. i have tried vlookup, countifs, etc, but i don't want it to be too scary looking if my boss should hover over any of the cells. i also don't want my boss to be able to delete the formula by accident (that's why the conditional formatting). I have been able to highlight if say the number of A shifts is >3, likewise with P shifts...and then >5 for D, E, and N shifts. But i can't figure out how to concisely do the same for combo shifts.

    attached is the sheet i will use (the names have been substituted).

    so far i have used a formula (found in Q45), but it only returns # of instances, not associated hours. the array values i placed under the total fields, B80:C84

    can anyone think of anything? i have also tried sumproduct, but it won't search for wildcard/partial cell text entry.

    this schedule is emailed out and i would like this formulary to be hidden.
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: conditional format based on sum of value assigned to partial text field

    why not just protect the worksheet, make sure those cells with formulas in are locked and hidden
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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