conditional formatting

[dmac13]

Hi is it possible to use conditional formatting to compaire 2 tables ?

Table 1 example - store level :-

Store Type Sales
1 A 100
2 A 50
3 B 70
4 C 30

Table 2 example - Company Level :-

Type Sales
A 150
B 70
C 30

is it possible to highlight the stores that are 50% under the company sales ? i.e if store 1 (type A) is 50% under the company sales total for A type stores, highlight red.

 

[GlennUK]

Are the tables in the same sheet?

 

[dmac13]

yes both tables are on the same spreadsheet

 

[GlennUK]

That's good then ... what are the cell ranges of your 2 tables, and I'll tell you a formula to use in the conditional formatting ( it'll be quite simple, using INDEX and MATCH )?

 

[dmac13]

thanks

the store level table is D5:G58 where column D = the type and column G is the value to be compaired.

the type level table is D64:G70, the columns are in the same positions

 

[GlennUK]

Select D6:G58, and do conditional formatting, formula is:

=$G6<(50%*INDEX($G$65:$G$70,MATCH($D6,$D$65:$D$70,0)))

and choose your fill colour.

Assuming that the first row of each table is titles.

 

[dmac13]

cheers and thanks for the help

 

[GlennUK]

Glad to help