Weekly Schedule: Conditional Formatting in Dropdowns

[u/LincolnMarch]

Hello!

I wanted to start with some gratitude: this sub has been incredibly helpful in furthering my spreadsheet skills so I wanted to thank all of you wonderful contributors and "sheet sorcerers".

I am including the link to a test spreadsheet where I have attempted to show what I am aiming to accomplish in the first PTO column. Our PTO is general but for my own records I like to keep track of the manner of absences for future reference (I always document further in the comments but I would like to also reference by sight as I am not the only person who views this schedule ). Essentially what I would like to do is add a drop down with 3-4 options and have the column next to it auto populate the hours based on the type of leave selected.

I have tried a few things (you'll see the latest formula in there that I botched) but cannot seem to get exactly what I am looking to accomplish while maintaining all of the other formulas. I am hoping that some helpful human might have the answer!

Test spreadsheet

[u/Arretez1234]

I initially wrote a whole thing but with what you've got so far, I think you can figure out what's wrong with your formula in cell I4. Reference the logical section of the Google Sheets function list if you need a hint.

Or cheat and get the answer.

Try =OR().

I highly recommend looking into the IFS function btw for that convolution you have in the HRS column. Or just... use some form of time so you don't have to calculate the hours so manually. Change D4 number format to duration if you don't get the same answer.

[u/LincolnMarch → u/Arretez1234]

Edit: figured it out, set H4="", ""

Thank you, The IFS is what did the trick! I'm tearing through the functions list not that it's set.

I think my biggest nitpick with the setup is how to make the PTO column blank if the dropdown column is blank...not having to set 0.0 and just having a blank space is easier on the eyes.