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 |
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
|
,
0 Comments
)