Conditionally format a cell based on another cells conditional format

sclark27

New Member
Joined
Mar 8, 2016
Messages
3
See link below with notes. The percentages in column E are conditionally formatted to show the top number of values called for in cell E3. How do I conditionally format the cells in column B that correspond to the numbers in column D that are the highest percentages?


 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
just use the same rule in column B
=$E5>=LARGE($E$5:$E$14,$E$3)
to highlight B

or is that NOT what you meant



Untitled spreadsheet.xlsx
ABCDE
1
2
3Top3
44
56018.42%
63118.42%
73240.63%
80340.63%
92428.57%
104515.38%
114628.57%
124736.36%
13289.76%
144918.42%
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:B14Expression=$E5>=LARGE($E$5:$E$14,$E$3)textNO
E5:E14Expression=$E5>=LARGE($E$5:$E$14,$E$3)textNO
Cells with Data Validation
CellAllowCriteria
E3List1,2,3,4


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B5:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$E5>=LARGE($E$5:$E$14,$E$3)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 
Upvote 0
So the top three percentages are in cells E7, E8, & E12. The corresponding numbers are 2, 3, & 7 respectively from column D. I would like the same corresponding numbers in column B to also highlight. In this case any cell that has the number 2, 3, or 7.
 
Upvote 0
what version of excel
this may be a messy answer - BUT needs to have the FILTER() function

AND another member maybe able to see a MUCH simpler solution

=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))

maybe very slow and may need a helper column
Untitled spreadsheet.xlsx
ABCDEFG
1
2
3Top3
4FALSE40FILTER - extract matching number FYI
5FALSE60018.42%2
6TRUE31118.42%3
7TRUE31240.63%7
8FALSE00340.63%
9TRUE21428.57%
10FALSE40515.38%
11FALSE40628.57%
12FALSE40736.36%
13TRUE2189.76%
14FALSE40918.42%
15TRUE31
16TRUE21
17FALSE60
18TRUE71
19TRUE31
20FALSE50
21TRUE71
22FALSE90
23FALSE50
24FALSE80
25TRUE31
26FALSE10
27FALSE10
28TRUE31
29FALSE60
30TRUE31
31FALSE60
32FALSE00
33TRUE21
34FALSE00
35TRUE31
36TRUE21
37TRUE71
38TRUE31
39FALSE60
40FALSE90
41TRUE21
42TRUE21
43FALSE10
44TRUE21
45FALSE80
46FALSE40
47FALSE00
48FALSE80
49FALSE40
50FALSE50
51FALSE10
52TRUE31
53FALSE60
54TRUE21
55TRUE71
56TRUE71
57TRUE71
58TRUE71
59TRUE21
60FALSE90
61TRUE31
62FALSE60
63FALSE90
64FALSE00
65FALSE50
66FALSE90
67FALSE10
68FALSE40
69TRUE71
70FALSE60
71TRUE21
72FALSE00
73FALSE60
Sheet3
Cell Formulas
RangeFormula
G5:G7G5=FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3))
A4:A73A4=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))
C4:C73C4=SUM(COUNTIFS(B4,$G$5#))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:B73Expression=ISNUMBER(MATCH($B4,FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3)),0))textYES
E5:E14Expression=$E5>=LARGE($E$5:$E$14,$E$3)textNO
Cells with Data Validation
CellAllowCriteria
E3List1,2,3,4



OR you can use a helper column and use the extreacted numbers that match
=SUM(COUNTIFS(B4,$G$5#))

Untitled spreadsheet.xlsx
ABCDEFG
1
2
3Top3
4040Helper column
5060018.42%2
6131118.42%3
7131240.63%7
8000340.63%
9121428.57%
10040515.38%
11040628.57%
12040736.36%
1312189.76%
14040918.42%
15131
16121
17060
18171
19131
20050
21171
22090
23050
24080
25131
26010
27010
28131
29060
30131
31060
32000
33121
34000
35131
36121
37171
38131
39060
40090
41121
42121
43010
44121
45080
46040
47000
48080
49040
50050
51010
52131
53060
54121
55171
56171
57171
58171
59121
60090
61131
62060
63090
64000
65050
66090
67010
68040
69171
70060
71121
72000
73060
Sheet3 (2)
Cell Formulas
RangeFormula
G5:G7G5=FILTER($D$5:$D$14,$E$5:$E$14>=LARGE($E$5:$E$14,$E$3))
A4:A73A4=SUM(COUNTIFS(B4,$G$5#))
C4:C73C4=SUM(COUNTIFS(B4,$G$5#))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:B73Expression=SUM(COUNTIFS($B4,$G$5#))textYES
E5:E14Expression=$E5>=LARGE($E$5:$E$14,$E$3)textNO
Cells with Data Validation
CellAllowCriteria
E3List1,2,3,4


and if an earlier version - should be able to pull out the list without an FILTER()
 
Last edited:
Upvote 0
Good information. Didn't use verbatim but was able to tweak it to work based on what you provided. I did use a helper column.
 
Upvote 0
great you are welcome

i would still add the version of excel
What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are: 2003/2007/2010/2013/2016/2019/2021/2024 (due April24) /365 subscription , also rather than show more than 1 version . if you have them , show which version you will be using the solution for.
An awful lot of new functions have now been added to the newer versions
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top