Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formats affect other cells
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Protection of Cells | Excel Worksheet Functions | |||
Creating a conditional format for a cell based on another cell's v | Excel Discussion (Misc queries) | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel | |||
counting cells with conditional formatting applied | Excel Discussion (Misc queries) | |||
Copying cells with conditional formatting | Excel Discussion (Misc queries) |