conditional formats affect other cells

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.

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(D10:D20,"vacation",E10:E20)

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

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)

--
Regards,
Tom Ogilvy


"golden322" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" wrote:

>
> 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(D10:D20,"vacation",E10:E20)
>
> this will sum hours in column E when the corresponding cell in column D
> is "vacation"
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=503745
>
>

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.

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" wrote:

>
> 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.
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=503745
>
>

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

That should make it easier to diagnose the problem

would you like me to just email you the workbook?

"daddylonglegs" wrote:

>
> Can you detail exactly what conditional formatting formulas you are
> using and in which cells?
>
> That should make it easier to diagnose the problem
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=503745
>
>

ok mail me at

daddylongleg@REMOVE_THIShotmail.co.uk