SOLVED

Conditional Format cell color based on value tied to specific value in dropdown list

%3CLINGO-SUB%20id%3D%22lingo-sub-1120738%22%20slang%3D%22en-US%22%3EConditional%20Format%20cell%20color%20based%20on%20value%20tied%20to%20specific%20value%20in%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120738%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20looked%20through%20a%20few%20conditional%20formatting%20threads%2C%20but%20did%20not%20find%20my%20answer.%20In%20the%20sample%20provided%20in%20a%20second%20step%20beyond%20the%20indexing%20i%20have%20already%20done%20I%20am%20trying%20to%20tie%20the%20month%20in%20the%20drop%20down%20list%20on%20the%20sheet%20labelled%20%22dashboard%22%20with%20the%20month%20and%20target%20in%20the%20sheet%20labelled%20%22target%22%20and%20have%20the%20dashboard%20cells%20reporting%20the%20segments%20colored%20Green%20or%20Red%20based%20on%20meeting%2Fsurpassing%20or%20failing%20to%20meet%20the%20target.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20respond%20in%20a%20detailed%20step%20by%20step%20fashion%20as%20I%20am%20a%20visual%20learner%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1120738%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-1121037%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20cell%20color%20based%20on%20value%20tied%20to%20specific%20value%20in%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20two%20rules%20for%20the%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20706px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166360i66C5B5F794B6DEB6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formulas%3C%2FP%3E%0A%3CP%3Ered%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24B2%3CINDEX%3E%3C%2FINDEX%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Egreen%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24B2%26gt%3BINDEX(Target!%24B%242%3A%24D%2413%2CMATCH(%24B%241%2CTarget!%24A%242%3A%24A%2413%2C0)%2CMATCH(%24A2%2CTarget!%24B%241%3A%24D%241%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor

Hello,

I looked through a few conditional formatting threads, but did not find my answer. In the sample provided in a second step beyond the indexing i have already done I am trying to tie the month in the drop down list on the sheet labelled "dashboard" with the month and target in the sheet labelled "target" and have the dashboard cells reporting the segments colored Green or Red based on meeting/surpassing or failing to meet the target. 

 

Please respond in a detailed step by step fashion as I am a visual learner  

1 Reply
Best Response confirmed by shade206 (Contributor)
Solution

@shade206 

You may apply two rules for the range

image.png

with formulas

red

=$B2<INDEX(Target!$B$2:$D$13,MATCH($B$1,Target!$A$2:$A$13,0),MATCH($A2,Target!$B$1:$D$1,0))

green

=$B2>INDEX(Target!$B$2:$D$13,MATCH($B$1,Target!$A$2:$A$13,0),MATCH($A2,Target!$B$1:$D$1,0))