If/Then formula triggered by cell color

%3CLINGO-SUB%20id%3D%22lingo-sub-1550946%22%20slang%3D%22en-US%22%3EIf%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550946%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon!%20I%20am%20trying%20to%20implement%20a%20staff%20calendar%20of%20sorts%20that%20keeps%20track%20of%20all%20available%20time%20off%20hours.%20I'd%20like%20to%20use%20a%20function%20that%20depending%20on%20the%20cell%20color%20chosen%2C%20would%20then%20subtract%20from%20an%20available%20balance%20of%20hours%20remaining.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20example%2C%20let's%20say%20I%20notated%20that%20my%20associate%20used%208%20bank%20hours%20on%20the%2016th%2C%20I'd%20like%20to%20color%20the%20cell%20red%20and%20have%20it%20substract%20that%20amount%20from%20the%20current%20total%20value%20in%20D2.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1550946%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1550997%22%20slang%3D%22en-US%22%3ERe%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550997%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742585%22%20target%3D%22_blank%22%3E%40Cmears%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ESee%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20conditional%20formatting%20to%20fill%20the%20cell%20in%20red%20if%20a%20number%20between%200%20and%20500%20in%20entered.%20That%20should%20cover%20it.%20Added%20a%20total%20hour%20at%20the%20end%20of%20your%20line%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551040%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551040%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742585%22%20target%3D%22_blank%22%3E%40Cmears%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EEnclosed%20the%20file%20with%20a%20proposed%20solution%20...%20if%20the%20answer%20has%20helped%20you%2C%20then%20please%20tick%20as%20the%20correct%20answer%20so%20that%20others%20can%20inform%20yourself%2C%20a%20little%20like%20would%20also%20be%20good%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551061%22%20slang%3D%22en-US%22%3ERe%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B-%20I%20feel%20like%20this%20should%20almost%20be%20a%20reverse%20conditional%20formatting%20with%20an%20If%2FThen%20function.%20Because%20I%20have%20separate%20categories%20of%20time%20off%20I%20would%20highlight%20a%20cell%20where%20an%20associate%20used%20%22bank%20hours%22%20red%2C%20then%20have%20it%20subtract%20from%20the%20D2%20cell.%20I'd%20highlight%20another%20color%20if%20the%20employee%20used%20his%20%22floating%20holiday%22%2C%20which%20would%20trigger%20a%20subtraction%20from%20E2.%20Hopefully%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551092%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551092%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742585%22%20target%3D%22_blank%22%3E%40Cmears%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20the%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20know%20nothing%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551132%22%20slang%3D%22en-US%22%3EBetreff%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551132%22%20slang%3D%22en-US%22%3EI%20see%20how%20this%20could%20work%2C%20but%20it%20would%20make%20the%20spreadsheet%20significantly%20larger%20and%20I'm%20trying%20to%20keep%20this%20as%20concise%20as%20possible.%20I%20have%20four%20different%20categories%20of%20time%20off%20of%20an%20individual%20could%20use%20on%20any%20given%20day%20w%2F%2030%2B%20employees.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551257%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%2FThen%20formula%20triggered%20by%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551257%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F742585%22%20target%3D%22_blank%22%3E%40Cmears%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EEnclosed%20the%20file%20with%20a%20proposed%20solution%20...%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20the%20answer%20helped%2C%20then%20please%20tick%20as%20the%20correct%20answer%20so%20that%20others%20can%20also%20find%20out%20from%20it.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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.

Highlighted

@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)
Highlighted

@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.

Highlighted

@Cmears 

 

see the file

 

Nikolino
Ich weiß dass ich nichts weiss (Sokrates)

Highlighted
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.
Highlighted

@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)