I have spent hours trying to figure out the best way to capture some data. This is done on a daily basis.
Need to track for several different employees specific PTO, including vacation, sick, and floating time off.
Then quarterly, I need to take the summary and subtract them. Here's where my coworker has started. She is typing in to the fields 8 V for 8 hours vacation time, 2 S for sick, etc. She has then done a conditional format where it will change the color of the cell dependent on the type of PTO it is.
I don't know the best way to capture this. And I can't get the SumByColor VBA I found online to work, so that's another issue altogether.
I don't want to get stuck overthinking something that could be handled in a much easier way. Suggestions?
I am including sample data below in a mini table.
Thank you in advance
Need to track for several different employees specific PTO, including vacation, sick, and floating time off.
Then quarterly, I need to take the summary and subtract them. Here's where my coworker has started. She is typing in to the fields 8 V for 8 hours vacation time, 2 S for sick, etc. She has then done a conditional format where it will change the color of the cell dependent on the type of PTO it is.
I don't know the best way to capture this. And I can't get the SumByColor VBA I found online to work, so that's another issue altogether.
I don't want to get stuck overthinking something that could be handled in a much easier way. Suggestions?
I am including sample data below in a mini table.
Thank you in advance
Sample from Dawn.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
4 | Last Name | 1/1 | 1/8 | 1/15 | 1/22 | 1/29 | 2/5 | 2/12 | 2/19 | ||
5 | Alfred | 8 V | 2 S | 8 V | 8 V | 8 FL | 2 S | 3 FL | |||
6 | Bonnie | 8 S | |||||||||
7 | Carol | 3 FL | |||||||||
8 | Donnie | 6 FL | 8 S | 3 V | 6 V | 6V | |||||
9 | Elena | 2 S | |||||||||
10 | Fred | 2 S | |||||||||
11 | Gary | 8 V | |||||||||
Option 3 - Pivot |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C4:I4 | C4 | =B4+7 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C6:I11 | Cell Value | contains "FL" | text | NO |
C6:I11 | Cell Value | contains "S" | text | NO |
C6:I11 | Cell Value | contains "V" | text | NO |
B5:B11,C5:F5,H5:I5 | Cell Value | contains "FL" | text | NO |
B5:B11,C5:F5,H5:I5 | Cell Value | contains "S" | text | NO |
B5:B11,C5:F5,H5:I5 | Cell Value | contains "V" | text | NO |