conditional formats affect other cells

[Guest]

I am putting together a calendar that tracks vacation, sick, holiday and
personal time used.

I created a drop down menu in one cell with those four option. In the
adjacent cell, I am putting options for hours: 8, 12, or 16 hours as another
drop down menu.

I am color coding the type of days requested for visual simplicity (vacation
= blue, sick = green, etc).

My question is two-fold:
1) how can I I get the color from the "day" cell on the left extend to the
"hours" cell on the right, once it is chosen?

2) How would I link the two cells so that I could get a monthly sum of the
hours of each type:
i.e.- when you choose "vacation" from the left drop-down menu, the
"vacation" function would then include the hours in the cell adjacent in the
sum function.
If you had chosen "sick" instead of "vacation", a different function would
record that value for the associated hours.
This way, you can sum up the four different types of days off requested in
separate furmulas.

So the type of day chosen in the drop down menu defines which function
calculates the hours value in the adjacent cell.

Thanks.

 

[daddylonglegs]

1. You say you are "colour-coding" do you mean using conditional
formatting? If you are and assuming "Vacation" etc. in D10 and hours in
E10...

select D10:E10
Format > CF > formula is
=$D10="vacation"
Select required formatting

Do this for all three options and D10 and e10 will both display the
same colour according to the category in D10

2. Do you mean you have multiple cells to sum? based on the above
something like

=SUMIF(D1020,"vacation",E10:E20)

this will sum hours in column E when the corresponding cell in column D
is "vacation"

 

[Tom Ogilvy]

In the conditional formatting dialog for the hours cell, change Cell Value
is to Formula is

Then enter a formula that refers to the "day" cell.


To calculate hours by type of day, use the sumif formula

=Sumif(B1:B10,"Sick",C1:C10)

as an example.

If the month was laid out on a big grid like a calendar and each day of the
week was two columns starting in B as an example (5 weekdays in a month and
5 rows of weeks)

=Sumif(B3:J7,"Sick",C3:K7)

 

[Guest]

I tried this, and couldnt get it to work the way I would like.

However, I thought it might be a little bit better if, for ex,
C9 stays clear colored, and has a drop down with four choices. Each option
will give
D9 one of four diffferent colors. But also will allow you to enter the hours
from the drop down menu in D9 without losing color.

I messed around with a few different ways, but couldn't get it to work. Do
you think you could help me out on this?

Thanks for the assist before.

Avi

 

[daddylonglegs]

Select D9 and apply conditional formatting as follows

condition 1
formula is
=C9="vacaton"
blue formatting

condition 2
formula is
=C9="sick"
green formatting

etc

 

[Guest]

Still doesn't work.

The correct box is finally coloring in. However,
Condition 1 turns condition 2's color;
Condition 2 & 3 turn condition 1's color.

And besides, once I go to the neighboring cell to choose a number of hours
from the dropdown menu, the colors in that cell disappear anyhow, and it's a
clear color with the numbers in it.

Any ideas?

 

[daddylonglegs]

Can you detail exactly what conditional formatting formulas you are
using and in which cells?

That should make it easier to diagnose the problem

 

[Guest]

would you like me to just email you the workbook?

 

[daddylonglegs]

ok mail me at

daddylongleg@REMOVE_THIShotmail.co.uk