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

[Guest]

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

 

[Dav]

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

 

[Pete_UK]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Dav]

Yes if you have another formula it will override it as it stops at th
first condition that is satisifed

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

Regards

Da

 

[Guest]

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]

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