Ask Your Question

Using data from two tables in conditional formatting

asked 2016-10-25 13:24:31 +0200

Joysn gravatar image

I have two pivot tables. Both have the same size / structure, but they visualize two different aspects. I can use conditional formatting to change e.g. the background color of the tables cells (e.g. <=0 -> gray, >0 -> light red). Now i want to change the formatting of one tables cells by combining the values of both tables, e.g. like this:

table1.cell = (0 or empty) -> light red (table1.cell = (0 or empty) and table2.cell >0) -> red table1.cell >0 -> normal

I tried to formulate a formula in the dialog for conditional formatting, but i don't know how to write the reference so that the formatting applies to all "connected" cells.

With connected cells i mean: table1.A1 is connected to table2.A1 table1.A2 is connected to table2.A2 .. table1.B1 is connected to table2.B1 table1.B2 is connected to table2.B2

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-10-25 23:56:57 +0200

mark_t gravatar image

First select Table1.A1. Add the conditional format for (table1.cell = (0 or empty) and table2.cell >0) -> red. Formula Is

(Sheet1.A1 = 0) AND (Sheet2.A1 = 0)

Add another conditional format for table1.cell = (0 or empty) -> light red Cell Value is, Equal to, 0

In the formulas above make sure you don't include $ or this stops the reference for other cells in the range from being adjusted.

Then in the box Cell Range specify the cells to apply the conditional format by selecting the box and then selecting the range of cells, example

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-25 13:24:31 +0200

Seen: 47 times

Last updated: Oct 25 '16