SOLVED

Sorting in conditional formatting based on the other column

%3CLINGO-SUB%20id%3D%22lingo-sub-2148433%22%20slang%3D%22en-US%22%3ESorting%20in%20conditional%20formatting%20based%20on%20the%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148433%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EI%20have%20a%20problem%20with%20sorting%20data%20in%20my%20model.%3CBR%20%2F%3EOn%20the%20first%20sheet%2C%20I%20have%20a%20price%20list%20with%20my%20products%20main%20prices%20and%20their%20latest%20variances.%3CBR%20%2F%3EAs%20you%20can%20see%20on%20the%20attached%20file%2C%20these%20variances%20could%20be%20positive%20(green%20color)%20and%20negative%20(red%20color).%3CBR%20%2F%3EOn%20the%20second%20page%2C%20I%20want%20to%20vlookup%20these%20variances%2C%20but%20as%20absolute%20values.%3CBR%20%2F%3EHowever%2C%20these%20absolute%20values%20must%20be%20conditional%20formatted%20by%20values%20from%20the%20first%20page.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20created%20rules%20which%20are%20working%20when%20I%20didn't%20sort%20my%20values%20from%20largest%20to%20smallest%20and%20vice%20versa.%3C%2FP%3E%3CP%3EWhen%20I%20sort%20them%2C%20everything%20is%20broking.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20simplified%20my%20model%20as%20much%20as%20I%20can.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2148433%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148500%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20in%20conditional%20formatting%20based%20on%20the%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148500%22%20slang%3D%22en-US%22%3EHi%20there%2C%3CBR%20%2F%3EHave%20you%20attached%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148571%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20in%20conditional%20formatting%20based%20on%20the%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481821%22%20target%3D%22_blank%22%3E%40Ilgar_Zarbaliyev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20I%20don't%20know%20why%20it%20didn't%20work%20before.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,
I have a problem with sorting data in my model.
On the first sheet, I have a price list with my products main prices and their latest variances.
As you can see on the attached file, these variances could be positive (green color) and negative (red color).
On the second page, I want to vlookup these variances, but as absolute values.
However, these absolute values must be conditional formatted by values from the first page.

I created rules which are working when I didn't sort my values from largest to smallest and vice versa.

When I sort them, everything is broking.

I simplified my model as much as I can.

 

 

 

3 Replies
Hi there,
Have you attached file?

@Ilgar_Zarbaliyev 

Yes, I don't know why it didn't work before.

Best Response confirmed by pawelj795 (New Contributor)
Solution

@pawelj795 

Change the CF rules in the sorted sheet to:

 

Green: =VLOOKUP(A2,Sheet1!$A$2:$D$9,4,0)>0.02

Red: =VLOOKUP(A2,Sheet1!$A$2:$D$9,4,0)<-0.02

 

Then it will work as you intend. 

Screenshot 2021-02-19 at 10.02.48.png