+ Reply to Thread
Results 1 to 8 of 8

conditional cell format based on cell in same row, previous column

  1. #1
    tamiluchi
    Guest

    conditional cell format based on cell in same row, previous column

    Hi,
    I have a spreadsheet that calculates employee leave based on input usage and
    accrual.
    Each employee has 3 rows (3 types of leave)
    for employee John Doe:
    Row 2: type P
    Row 3: type M
    Row 4: type X

    Each month has 3 columns:
    for March:
    Column C: available
    Column D: taken
    Column E: accrued

    The next month's available is column F (C2-D2+E2) and so on throughout the
    year.

    In addition to the 3 row alternate shading to separate the employees, I need
    the font in any cell in any month's 'Taken' column to turn red when the
    'Taken' amount is greater than the 'Available' amount for each of the 3 leave
    types for each employee. How do I accomplish that?

    I hope I'm being clear. I've tried a dozen things and I can't get anything
    to work other than manually entering conditional formatting for every cell in
    every 'taken' column. No way do I have time for that.

    I know to use the conditional formatting for when such-and-such cell value
    is greater than such-and-such cell. But how do I apply it to the entire
    spreadsheet? When I try to drag the formatting down from the first cell,
    every cell changes when it's greater than the value of that first 'available'
    cell, not the 'available' cell in the same row.

    I guess I just don't know the right language for the greater than blank to
    say the cell in the same row, previous column.

    Please, someone, help me. My deadline for this is 3pm today and I've been
    working on it on and off for more than a week.

    Thanks in advance,
    Tammie


  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Try entering the following in conditional formating as a formula in cell d2
    =C2<D2 and choose the colour red

    It can then be pasted down with paste special as a format to all the other cells.

    If it does not work in the first cell check excel has not enclosed it in quotes

    Regards

    Dav

  3. #3
    Pete_UK
    Guest

    Re: conditional cell format based on cell in same row, previous column

    I hope you are on a different time zone than me - it's just after 3
    here!

    Highlight the cells D2 to D_whatever, with D2 as the "activecell", then
    Format | Conditional Format then select Formula Is rather than Cell
    Contents and enter this formula:

    D2>C2

    then click the Format button and choose the effect/font/colour that you
    wish and OK.

    "_whatever" is the bottom cell that you use in column D (depends how
    many employees you have). Then you can copy and paste this range of
    cells to G2 downwards, then J2 downwards etc for each month of the
    year. You could also use the Format Painter for this last part.

    Hope this helps.

    Pete


  4. #4
    tamiluchi
    Guest

    Re: conditional cell format based on cell in same row, previous co

    It really seems that formula should work, but it just doesn't. No quotes. I
    can make it work in other spreadsheets, but not this one. I think my
    condition 1 for alternating 3 shaded rows may be over riding condition 2
    somehow.

    "Dav" wrote:

    >
    > Try entering the following in conditional formating as a formula in cell
    > d2
    > =C2<D2 and choose the colour red
    >
    > It can then be pasted down with paste special as a format to all the
    > other cells.
    >
    > If it does not work in the first cell check excel has not enclosed it
    > in quotes
    >
    > Regards
    >
    > Dav
    >
    >
    > --
    > Dav
    > ------------------------------------------------------------------------
    > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
    > View this thread: http://www.excelforum.com/showthread...hreadid=538457
    >
    >


  5. #5
    tamiluchi
    Guest

    Re: conditional cell format based on cell in same row, previous co

    It's only 9:40 a.m. here, so I have a little time. And if this part isn't
    perfected, it'll be alright, I'll just have to manually check every 'Taken'
    amount to make sure it doesn't exceed the 'Available' amount, like I have
    time for that! ha ha

    I can make this formula work in "test" areas of the spreadsheet which do not
    contain condition 1. I think condition 1 is interfering with condition 2. My
    condition 1 for alternate 3 row shading is: =MOD(ROW()-2,3*2)<3. I just don't
    know exactly what the interference is and how to stop it.

    "Pete_UK" wrote:

    > I hope you are on a different time zone than me - it's just after 3
    > here!
    >
    > Highlight the cells D2 to D_whatever, with D2 as the "activecell", then
    > Format | Conditional Format then select Formula Is rather than Cell
    > Contents and enter this formula:
    >
    > D2>C2
    >
    > then click the Format button and choose the effect/font/colour that you
    > wish and OK.
    >
    > "_whatever" is the bottom cell that you use in column D (depends how
    > many employees you have). Then you can copy and paste this range of
    > cells to G2 downwards, then J2 downwards etc for each month of the
    > year. You could also use the Format Painter for this last part.
    >
    > Hope this helps.
    >
    > Pete
    >
    >


  6. #6
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Yes if you have another formula it will override it as it stops at the first condition that is satisifed

    Try using c2<d2 as your first condition and the shading as your second condition, this should work, otherwise it will just shade. You never meantioned any other conditional formats before!

    Regards

    Dav

  7. #7
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Yes if you have another formula it will override it as it stops at the first condition that is satisifed

    Try using c2<d2 as your first condition and the shading as your second condition, this should work, otherwise it will just shade. You never meantioned any other conditional formats before!

    Regards

    Dav

  8. #8
    tamiluchi
    Guest

    Re: conditional cell format based on cell in same row, previous co

    Thank you so much, Dav. I truly cannot explain why it did not occur to me to
    switch the conditions, especially considering the absurd amount of time I've
    spent on this.
    I did mention in my first response that I wanted the 'red' condition to
    occur in addition to.....
    Sorry if I wasn't clear that the alternate 3 row shading was a conditional
    formula.
    Thank you again for your help. It's greatly appreciated.
    Tammie

    "Dav" wrote:

    >
    > Yes if you have another formula it will override it as it stops at the
    > first condition that is satisifed
    >
    > Try using c2<d2 as your first condition and the shading as your second
    > condition, this should work, otherwise it will just shade. You never
    > meantioned any other conditional formats before!
    >
    > Regards
    >
    > Dav
    >
    >
    > --
    > Dav
    > ------------------------------------------------------------------------
    > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
    > View this thread: http://www.excelforum.com/showthread...hreadid=538457
    >
    >


+ 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