Home

Conditional Formatting Relative References Not Working

%3CLINGO-SUB%20id%3D%22lingo-sub-457104%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457104%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20currently%20working%20on%20writing%20a%20conditional%20formatting%20rule%20that%20will%20highlight%20a%20cell%20if%20the%20value%20of%20a%20cell%20in%20column%20B%20(B1)%20is%20within%205%25%20of%20another%20cell%20(A1)%20in%20column%20A.%26nbsp%3B%20I've%20been%20able%20to%20figure%20out%20this%20formula%3A%26nbsp%3B%3DOR(%24A1*1.05%26lt%3B%24B1%2C%20%24A1*0.95%26gt%3B%24B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20conditioning%20formatting%20rule%20works%20perfectly%20when%20I%20compare%20A1%20to%20B1%20but%20when%20I%20try%20to%20copy%20the%20formula%20to%20subsequent%20cells%20to%20compare%20A2%20to%20B2%2C%20A3%20to%20B3%2C%20the%20formula%20does%20not%20work%20as%20it%20continues%20to%20reference%20the%20A1%20cell%20to%20compare%20itself%20to%20cells%20in%20the%20B%20column.%26nbsp%3B%20Could%20anyone%20help%20me%20figure%20out%20what%20I%20am%20doing%20incorrectly%20to%20get%20this%20formatting%20rule%20to%20work%3F%20I%20do%20not%20want%20to%20have%20to%20recreate%20this%20formula%20for%20each%20cell%20in%20order%20to%20get%20the%20conditional%20formatting%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-457104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457200%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457200%22%20slang%3D%22en-US%22%3ERemove%20the%20%24%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457497%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321593%22%20target%3D%22_blank%22%3E%40Laura_B%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20formula%20shows%20if%20A%20is%20on%20more%20than%205%25%20more%20or%20less%20than%20B%2C%20and%20you%20have%20correct%20highlighting%20for%20that.%20Perhaps%20you%20need%20another%20formula%2C%20like%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108981iC0BF9139E280F163%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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457498%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321591%22%20target%3D%22_blank%22%3E%40stv1vy%3C%2FA%3E%20%2C%20if%20use%20relative%20reference%20when%20values%20in%20B%20will%20be%20compared%20with%20ones%20in%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Laura_B
Occasional Visitor

Hi - I am currently working on writing a conditional formatting rule that will highlight a cell if the value of a cell in column B (B1) is within 5% of another cell (A1) in column A.  I've been able to figure out this formula: =OR($A1*1.05<$B1, $A1*0.95>$B1)

 

The above conditioning formatting rule works perfectly when I compare A1 to B1 but when I try to copy the formula to subsequent cells to compare A2 to B2, A3 to B3, the formula does not work as it continues to reference the A1 cell to compare itself to cells in the B column.  Could anyone help me figure out what I am doing incorrectly to get this formatting rule to work? I do not want to have to recreate this formula for each cell in order to get the conditional formatting to work.

 

Thanks!

3 Replies

@Laura_B ,

 

Your formula shows if A is on more than 5% more or less than B, and you have correct highlighting for that. Perhaps you need another formula, like here

image.png

 

@stv1vy , if use relative reference when values in B will be compared with ones in C

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies