View Question
Q: Conditional formatting in Excel 97 ( Answered ,   0 Comments )
 Question
 Subject: Conditional formatting in Excel 97 Category: Computers > Software Asked by: rainman999-ga List Price: \$20.00 Posted: 19 May 2006 13:53 PDT Expires: 18 Jun 2006 13:53 PDT Question ID: 730498
 ```I have a spreadsheet (do not know how to show you it) that I need to set up with conditional formatting for holidays i.e. only allow 20% of the staff off at any one time. If one member of staff is off on holiday turn the weeks cells yellow, if the 20% limit of people on holiday is reached turn the cells red.```
 ```Hello Rainman999, The assuming you have a few extra cells to do some intermediate calculations, the following method should be able to do what you want. For the tests I performed, I had an arrangement of cells like: - Column A - names of the staff - Columns C through E - Vacation / holiday days. A "Y" in the cell indicates a person wants to take vacation that day, a "N" indicates they do not. - The three rows at the bottom of each column have a formula as described below. [1] At the bottom of the first column (was C8 in my example), enter a formula like the following =COUNTIF(C\$2:C\$7,"Y")=0 Adjust the range of cells to accommodate your situation. For example, you should refer to the first / last cell in the column with vacation indicators. In addition, if you use a different string instead of "Y", to indicate a vacation on a holiday, replace the "Y" with that string. If you use something more complicated that a simple string, indicate that in a clarification request and I can adjust the formula. [2] In the next cell below, enter a formula like the following: =COUNTIF(C\$2:C\$7,"Y")>=1 (same adjustment applies) [3] In the next cell below, enter a formula like the following: =COUNTIF(C\$2:C\$7,"Y")>=2 (same adjustments) [or whatever condition you want for the "20% rule"] [4] Select the three cells with these formulas, copy / paste into the remaining columns with holiday / vacation days. At this point the spreadsheet should have a set of true / false values at the bottom of each column. Only ONE of the three cells in each column should be "TRUE", the other two should be "FALSE". [5] Select all the cells in a column that indicate the vacation / holiday status. Use the menu Format -> Conditional Formatting... [the specific menu / prompts may be slightly different] and make the following entries: Condition 1, Formula Is, =C\$10 [adjust to the row / column of the third formula entered] Format... Select Patterns, select the Red Color and OK Condition 2, Formula Is, =C\$9 [adjust to the row / column of the second formula entered] Format... Select Patterns, select the Yellow Color and OK Condition 3, Formula Is, =C\$8 [adjust to the row / column of the first formula entered] Format... confirm no patterns are selected and OK At this point, you can test the formulas and conditional formatting for this column to see how it works. [6] Apply the same formatting to all the other columns. There are a couple of different ways this can be done. The most straight forward is to copy / paste the cells from the formatted column to the other columns (though this removes any holiday / vacation data already entered). If "Paste Special" is available, use that to paste the formats to the other columns if you need to preserve the data. At this point, the spreadsheet changes should be complete. If the spreadsheet does not operate as expected or if some part of the answer is unclear, please request a clarification so I can correct the answer prior to rating. To find additional information on this topic, search Excel help with phrases like conditional format or with phrases on Google such as "Excel 97" conditional format Excel 97 conditional format In the pages found, there are a number of different "applications" that conditional formatting have been used for such as hiding errors, highlighting out of range values, and so on. Good luck with your work. --Maniac``` Request for Answer Clarification by rainman999-ga on 20 May 2006 04:11 PDT ```Hi, Thanks very much for the very detailed answer you sent me,I hate to seem THICK, but perhaps you can help me out (I'm not that Excel savvy) here. I've put the spreadsheet concerned at this web address http://esnips.com/web/rainman999sBusinessFiles I need each shift (i.e. 06-00hrs > 14-00hrs; 14-00hrs > 22-00hrs; and the 22-00hrs > 06-00hrs) to all be "independant" to each other so that each shift has a minimum of 80% attendance. How do I incorporate the answer you gave me into that? Sorry to be dense, but that is why I came here to ask someone smarter than me in the first place ;o) Regards Paul.``` Clarification of Answer by maniac-ga on 20 May 2006 11:12 PDT ```Hello Rainman999, Not a problem - you do have a very complicated sheet to update. I downloaded a copy based on reading it (and your request), I assume you want separate calculations for: - Rows 43 to 55 (3 shift A, starts with 2 to 10 shift) - Rows 57 to 69 (3 shift B, starts with 10 to 6 shift) - Rows 71 to 84 (3 shift C, starts with 6 to 2 shift) - Rows 86 to 94 (weekend shift) If you need to incorporate the other rows (e.g., "shift 1" or "shift 2") - let me know and I can adjust the formula for you. [I can see this can be complicated by the changing shift periods for the A, B, and C shifts] Based on that - I made the following updates: Cell EQ100 is =COUNTIF(EQ\$43:EQ\$55,"H")=0 Cell EQ101 is =COUNTIF(EQ\$43:EQ\$55,"H")>=1 Cell EQ102 is =COUNTIF(EQ\$43:EQ\$55,"H")>=2 I reformatted the cells (reduced the font size to 6, and a boundary around them to "group them" visually). I copied these three cells, three times to EQ103 to EQ105, EQ106 to EQ108, EQ109 to EQ111. I then updated them to refer to the proper cells: EQ103 to EQ105 now have EQ\$57:EQ\$69 EQ106 to EQ108 now have EQ\$71:EQ\$84 EQ109 to EQ111 now have EQ\$86:EQ\$94 They should now show the true / false results for each shift. At this point, you should be able to copy / paste these cells into the other columns and the formulas will show the correct results for each day / shift combination. Now, apply the conditional formatting to the cells and referring to each of the three cell groups. Select EQ42 through EQ55 and use the following formulas / formats. =EQ\$102, red format =EQ\$101, yellow format =EQ\$100, no format When you enter OK, the cells should turn yellow (there is one H in this range). I suggest at this point you confirm all the other cell formats are the way you want (centered text, bold, etc.). Then when you copy / paste special the format to other cells in these rows the results should appear OK. Now repeat for EQ57 through EQ69 and use the following formuals / formats. =EQ\$105, red format =EQ\$104, yellow format =EQ\$103, no format And repeat the formatting check & copy / paste special as identified above. And repeat for EQ71 through EQ84 using the following formulas and formats. =EQ\$108, red format =EQ\$107, yellow format =EQ\$106, no format And repeat the formatting check & copy / paste special as identified above. And repeat for EQ86 through EQ94 using the following formulas and formats. =EQ\$111, red format =EQ\$110, yellow format =EQ\$109, no format And repeat the formatting check & copy / paste special as identified above. When I did this in my copy - the status changed near cell GP71 (Red to Yellow) - that should also help confirm that this is working the way you expected. --Maniac``` Request for Answer Clarification by rainman999-ga on 25 May 2006 13:35 PDT ```Hi Maniac, Thanks again for the information, guess I'm a real dipshit, because I got as far as the true false section and got confused AGAIN. If I change the money to \$40 would it be possible for you to enter the formula for me in the 2006/2007 tabs, and then tell me where to download it from? Then I can try (not very Excel literate) and understand it later at my leisure. Regards Paul``` Clarification of Answer by maniac-ga on 30 May 2006 18:58 PDT ```Hello Rainman999, Not a problem - add a tip if you want to. Take a look at http://homepage.mac.com/mhjohnson/maniac-ga/rainman999/bodyhardRegister-97.xls which is the copy I created. It should perform as you asked but if you have any further problems, please make another clarification request. --Maniac```
 rainman999-ga rated this answer: and gave an additional tip of: \$20.00 ```Waht can I say, perfect, "EXACTLY" what I was after. Many thanks. Paul.```