If/Then formula triggered by cell color

Copper Contributor

Good afternoon! I am trying to implement a staff calendar of sorts that keeps track of all available time off hours. I'd like to use a function that depending on the cell color chosen, would then subtract from an available balance of hours remaining. 

 

In the attached example, let's say I notated that my associate used 8 bank hours on the 16th, I'd like to color the cell red and have it substract that amount from the current total value in D2. 

 

Any help is greatly appreciated!



6 Replies

Hi @Cmears,

See attached file. 

I used conditional formatting to fill the cell in red if a number between 0 and 500 in entered. That should cover it. Added a total hour at the end of your line too.

@Cmears 

Enclosed the file with a proposed solution ... if the answer has helped you, then please tick as the correct answer so that others can inform yourself, a little like would also be good :)


Nikolino
I know I don't know anything (Socrates)

@Bennadeau - I feel like this should almost be a reverse conditional formatting with an If/Then function. Because I have separate categories of time off I would highlight a cell where an associate used "bank hours" red, then have it subtract from the D2 cell. I'd highlight another color if the employee used his "floating holiday", which would trigger a subtraction from E2. Hopefully that makes sense.

@Cmears 

 

see the file

 

Nikolino
Ich weiß dass ich nichts weiss (Sokrates)

I see how this could work, but it would make the spreadsheet significantly larger and I'm trying to keep this as concise as possible. I have four different categories of time off of an individual could use on any given day w/ 30+ employees.

@Cmears 

 

Enclosed the file with a proposed solution ...

if the answer helped, then please tick as the correct answer so that others can also find out from it.


Nikolino
I know I don't know anything (Socrates)