Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional cell format based on cell in same row, previous column
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: D2C2 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: D2C2 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional cell format based on cell in same row, previous column
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional cell format based on cell in same row, previous column
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |