EDIT: On posting, I notice that you had figured this out shortly after i started experimenting and writing the revised and illustrated instructions below. No harm done; it was an interesting exercise (interrupted by dinner, which might explain why I posted so long after the problem had been solved), and a worthwhile one. I the hope it might prove useful to other readers, I'll leave it up.
Regards,
Barry
Hi JK,
Badunit's suggestion is a brilliant one, coming from an experiment that i hadn't tried before.
I think the "tables don't sit very flush together" because you've misinterpreted one step in **'s instructions.
Here's a second go at them, with illustrations and some edits. My approach here is to create a three column table, set up the conditional formatting, then use that base to build the full table.
Start by creating a table with one header row and one header column. Note that it is essential that column A be defined as a Header column. Select the cells to receive the conditional formatting:
Two things to note: All cells to be formatted have been selected.
Because your two conditions are mutually exclusive, only one rule is necessary.If that one is TRUE, the conditional colour (red) applies; otherwise, the cell's base fill colour (green) will appear.
At this point all cells are compared to B2, and formatted accordingly.
Next step:
Select cell C3. In the conditional format rule, click the cell reference to B2 to select it. Now click cell B3 to change the reference to that cell.
Repeat for cells C4 and C5,
Here C4 has been selected, and I have clicked on the original cell reference to B2, selecting it and highlighting it. When I click on cell B4, the highlighted B2 will change to B3.
Note that I've changed the goal values in B3 and B4 to illustrate that C3's format is now dependent on the goal in B3, while C4's is still dependent on the value in B2.
When all cell references in column C have been corrected, the table should act like this:
Click on any cell in the table to show the row and column reference tabs. Then click on the B column tab to select it, and shift click the C column tab to add it to the selection.
Position the mouse pointer just to the left of the B, press the (left) mouse button and drag down ant to the right.
You will see a semi transparent copy of columns B and C move away from the table. Continue to drag until the copy is completely separated from the original table, then release the mouse button to drop the table onto the sheet. The result should be similar to that below:
Click on the new table's icon in the Sheets list to the left of the main window, then press command-D once for each pair of columns you need.
Key step: Joining the tables.
Separate one of the small tables from the stack produced by duplicating.
Click on any cell, then click on the column A tab and shift-click on the column B tab. If you've followed those directions, the table should now appear similar to the lower and rightmost one in the image above.
Position the mouse pointer just to the left of the A, press the mouse button and drag the table onto the three column table to the left. When you see the thick blue line at the right edge of the three column table (see below), release the mouse button.
The two tables will join to form a single five column table.
Repeat with the next small table (shown above) and with the remaining table to gather them all into one.
When you have joined all the tables, check the conditional formatting rules across one row. I found, when I did this, that the first set (in column C) had switched allegiance from the goals in column B to those in column D. No idea why.
Fixing them, however, is a less onerous task than fixing ALL of the conditional formatting cell references.
Regards,
Barry
Message was edited by: Barry (opening note added)
