Several conditional formatting solutions needed (Excel 2003)

[Chasden]

Yes, i know only three conditional formats are permitted per cell. That's not exactly what i'm asking for.

Below is an example of the file i am working on (all names and ID numbers were entered using a random name/number generator).
http://dl.dropbox.com/u/41450186/Book1.xls

And here is a screenshot if you'd rather not look at the spreadsheet itself (Click for full size):


I can manage this file manually with no problem. The problem is that my company now wants to give my file to everyone else in the district to use, and it's not so easy to explain to others how to use it!

Background: The large names in bold are supervisors, and the smaller names are newly-hired employees assigned to those supervisors. The supervisor must "check in" with the new employee every day for the first 30 days of employment, and have a friendly chat to help the emlpoyee feel "at home" in their new job. This file tracks the documentation that the supervisors must provide as proof that they are following this procedure. The percentage score indicates the percent compliance.

Here is what i'm trying to do:
1) Based on the date entered in the employee's "Hire Date (Most Recent)" column, Excel should find that date in the top row, and fill in that day + the next 30 days (31 total) with yellow. In my current file, i do this manually.

2) Based on the same date above, count 4 days ahead, and color that cell grey, and enter "LO" in that cell (employees do not work on their 5th day at the company). I do this manually at the moment.

3) In the current file, entering a "1" changes the cell to green, entering "LO" changes the cell to grey, and entering a "0" does nothing to the cell, because in Excel 2003 all blank cells are interpreted as "0".

My boss says people won't understand 1's and 0's, so he wants me to use "y" and "n", or "yes" and "no", instead. I still need "y" (or yes) to count as a "1", and "n" (or no) to count as a "0" though, or the percentage formulas won't work. How do i do this?

4) Optionally, i'd also like to highlight today's date in the top row for ease of entering data.

I know i'm asking A LOT, but i really do appreciate your help. The district is already using several other files i've created, and now my boss wants to share this one as well. I'm not willing to share it in its current state, so any way to make it more "user-friendly" would help us a great deal. Take care!

 

[xenou]

Hi,

On the 0's and 1's -- just use Y or N per your bosses request. Data validation in the cells will help to make sure the users knows this (you can create a warning of the wrong value is entered). The number of Y's and N's can be counted with SUMIF() formulas (I would test that it works with upper and lowercase - offhand I don't know).

On filling out the yellow automatically -- I would just work with your hire date and the conditional formatting a little further. Maybe something like:
(Date Greater Than or Equal to Hire Date) - (Count of Previous Dates Greater Than or Equal to Hire Date)


This is going to be:

• a number between 1 and 30 that contains a 4 (format grey)
• a number between 1 and 30 that contains a Y (format green)
• a number between 1 and 30 that contains a N (format yellow)
• and otherwise an number not between 1 and 30 (do nothing with conditional formatting)

Untried.

 

[Chasden]

Thanks for the reply.

What i ended up doing while waiting for a reply, was to use data validation with drop down box, where the choices are 1, 0, and LO. The Input message says:

Enter "1" if the contact was entered.

Enter "0" if the contact was not entered.

Enter "LO" if the employee was laid off for this day.

Leave the cell blank if there is no information for this day.

The stop message says the same thing, except it adds that you can press Backspace or Delete to clear the current contents.

I experimented with adding Names (Insert -> Name...) to interpret Y as 1 and N as 0, but i couldn't get it to work. I'm sure it's doable, but i couldn't figure it out.

I also created a VERY detailed help sheet in the original file. My boss and the co-workers who saw it said that the file is foolproof with that help file and the drop down boxes, and that whoever handles the file at other branches shouldn't have a problem maintaining it (the assumption here is that each branch will have their "go-to" computer guy handling this stuff). It's just too bad i can't get the dates to color automatically.

 

[xenou]

Seems like you should be able to create the conditional formatting. My rough outline was incorrect though:

a number between 1 and 30 that contains a 4 (format grey)
a number between 1 and 30 that contains a Y (format green)
a number between 1 and 30 that contains a N (format yellow)
and otherwise an number not between 1 and 30 (do nothing with conditional formatting)

Should be (using one's and zero's again instead of Y/N):

• a number that is equal to 4 (format grey)
• a number between 1 and 30 for a cell that contains 1 (format green)
• a number between 1 and 30 for a cell that contains 0 (format yellow)
• and otherwise an number not between 1 and 30 (do nothing)

 

[Chasden]

Well, the 30 cells that need to change to yellow are actually going across, and won't actually have numbers in them. When they do have numbers, they will only have 1s or 0s, not numbers between 1-30.

My conditional formats right now are (and remember Excel 2003 only allows 3 at a time):
Value of "1" turns the cell green
Value of "LO" turns the cell gray

I can't have a "0" conditional format because blank cells count as 0s. There is no "Stop" feature in conditional formatting in Excel 2003. I have 2007 on my home computers, but it wouldn't do any good if the rest of the company uses 2003.

 

[xenou]

When I say a number between 1 and 30 I mean based on a comparison of the date (at the top of the column) to the hire date, using a formula that I described as:

(Date Greater Than or Equal to Hire Date) - (Count of Previous Dates Greater Than or Equal to Hire Date)

On the employees hire date, we get 1 from this formula, on the day after that 2 ... and so on. It also returns negative numbers and numbers greater than 30 when we are before or after the 30 day period of interest. Only the cells corresponding to days 1 to 30 are relevant, so the conditional format checks this out first. Day 4 is special so the conditional format checks this next. And finally it is only a question of whether there is a 1 or a zero in the cell. Three conditions.

Note that you would do this by creating a formula for the conditional formatting conditions, not by basing the conditional format on the cell value alone. Your formula could return anything - 0, 1, 2, or 3 for instance, or 'A', 'B', 'C', or 'D' (corresponding to the four possible states, including the "do nothing" state)

 

[Chasden]

Thanks again for the reply.

I'm not sure i know how to do that, then. What you're saying is, i can set conditional formatting in one cell, to look across at a different cell in the same row, subtract it from another different cell in the same column, and color the cell based on the difference?

Would i need to set this up in every individual cell, or would the formatting understand that, if the cell is H9, that it's comparing the hire date in row 9 (at C9) to the date in column H (at H3)? In other words, if i applied this conditional format to all the other cells (like K11 for example), would the formatting understand that it now should compare C11 and K3, or would they also just look at C9 and H3 since it's the same conditional format?

 

[xenou]

Correct. Actually you just need to return true or false. Something like these three formulas:

=IF(AND(B$2-$D4>=0,B$2-$D4<31),IF(B$2-$D4=4,TRUE,FALSE),FALSE)
=IF(AND(B$2-$D4>=0,B$2-$D4<31),IF(D9=1,TRUE,FALSE),FALSE)
=IF(AND(B$2-$D4>=0,B$2-$D4<31),TRUE,FALSE)

The conditions cascade so you only need to put them in the right order. (the first true condition evaluated stops there in Excel 2003). You use absolute/relative references to refer to different cells correctly. You refer to the cell that the conditional formula applies to by using its own address - using a relative address seems to work correctly so that as you copy or drag the format to other cells it goes with them: I would test with some very simple formulas and formats to get the hang of it. Something like:

=IF($A$1=1,IF(A2=1,TRUE,FALSE)
You should be able to put this in A2 and pull it down. Then anytime A1 does not have a 1 in it, you should get no conditional formatting. If A1 does have a 1 in it you get conditional format when A2 has a 1 in it, otherwise not.