9 Replies Latest reply: Apr 30, 2013 9:16 AM by Michaël D
Level 1 (0 points)

Hi

I am having a small event later this month and got stuck with an issue.

I made a spreadsheet where one column says how much money people have already paid. This is displayed next to a persons name.

The people who already confirmed have a green background, the ones that haven't, have a red background.

At the end of the document, I made a total of how much money needs to be collected. But since it's still possible that some people call in absent, I need the total to be adjusted everytime I change the color of the cell that contains the persons name.

Here you can see a picture of how my situation is:

http://cl.ly/image/2m1o2o0T4104

I hope someone can provide me with a fast answer.

MacBook Pro, OS X Mountain Lion (10.8.3)
• ###### 1. Re: How do I match a result to the color of certain cells?
Level 7 (29,180 points)

Hi Michael,

Let's start by moving the screen shot into this thread, where it can be seen and referred to without having to flip between pages in the browser:

Formulas cannot detect the colour of the fill in a cell.

But you could approach it from a different direction: Cell fill colour can be set by rules that compare the value in a cell with a fixed value or the value in another cell.

Similar conditions, can be used in a SUMIF or SUMIFS based formula to determine which numbers are included in a sum.

What rule do you follow to determine the background colour of the name cell? Could you use a checkbox to dictate that change? Or a popup menu?

What is the purpose of the checkboxes in column C? When do they get checked?

More details on the overall problem, please.

Regards,

Barry

• ###### 2. Re: How do I match a result to the color of certain cells?
Level 1 (0 points)

Hi Barry

The checkboxes in column C show if a depth is completely paid or not. This is because in some cases I don't receive the total amount of money that a person owes me at once.

The background color in the name cells is determined wether a person has decided to come or not. The moment a person confirms that he / she will come, I change the cell color to green. But even if a person has confirmed, that doesn't mean that he / she has already paid their complete depth. That's what columns B and C are for. The background color only exists to indicate if a person will come or not.

The basic price is 63,45 EUR. So that amount has to be multiplied by the amount of green cells. This is how I determine how much money needs to be collected.

Could you please tell me how to set these rules to cells and / or how the SUMIF works?

Thanks!

Michaël

• ###### 3. Re: How do I match a result to the color of certain cells?
Level 7 (28,995 points)

Michaël,

Short of an Applescript solution written by one of the forum's experts like Hiroto, you're best approach would be to manually tick a checkbox in a new auxiliary column for every green confirmation signal. The checkbox can then be used in a formula.

Unless you have thousands of these cases, the manual approach would be the quickest.

Jerry

• ###### 4. Re: How do I match a result to the color of certain cells?
Level 7 (29,180 points)

Hi Michael,

Jerry's suggestion is more or less where I was going as well.

Here's an example, with the checkbox placed in a (new) column A, and conditional formatting applied to the cells in column A, using the rule shown below the table. Default fill colour for column A has been set to the 'salmon' shade shown.

The next step gives the appearance of colouring the whole row for each checkbox checked. It uses an auxiliary table that copies the value from each row of column A, then applies a similar conditional formatting rule. Here the two tables are shown separately. Note that the CF rule also sets the text colour to the same green hue as the fill.

The final steps are to set Fill in the main table to 'none' (white square with diagonal red slash), then move the auxiliary table to the back, then slide it under the main table, allowing the colours to show through. I've added some text to the main table before doing this, so the effect can be seen.

Regards,

Barry

• ###### 5. Re: How do I match a result to the color of certain cells?
Level 5 (5,025 points)

Hello

Barry has already shown the authentic way of doing this in Numbers.

Here's another way using AppleScript although you need to run the script manually when you update the table, which is the major drawback.

To use this, copy the code lised below in new document of /Applications/Utilities/AppleScript Editor.app and run it. The script will pick the colour to be totalled from the last cell in column B and calculate the total as described. (You need to set the fill colour of the last cell in column B, which is supposed to be the cell for the total, to the green colour.)

(*
The sum cell S = the last cell of column B
The sum colour G = the fill color of S
The value of S = (unit) * (count of rows in column B whose fill colour is G)
*)
_main()
on _main()
set unit to 63.45 -- unit price

tell application "Numbers"
tell document 1's sheet 1's table 1
tell column 2
set bg to cell -1's background color
set n to count (cells whose background color = bg)
set cell -1's value to unit * n
end tell
end tell
end tell
end _main

Hope this may be of some help,

H

• ###### 6. Re: How do I match a result to the color of certain cells?
Level 5 (5,025 points)

Oops. The previous script counts the cell for the total itself and adds extra one. Sorry. Corrected code follows.

(*
The sum cell S = the last cell of column B
The sum colour G = the fill color of S
The value of S = (unit) * ((count of rows in column B whose fill colour is G) - 1)
*)
_main()
on _main()
set unit to 63.45 -- unit price

tell application "Numbers"
tell document 1's sheet 1's table 1
tell column 2
set bg to cell -1's background color
set n to count (cells whose background color = bg)
set cell -1's value to unit * (n - 1)
end tell
end tell
end tell
end _main

All the best,

H

• ###### 7. Re: How do I match a result to the color of certain cells?
Level 1 (0 points)

Hi Barry

By now I have succeeded only to make the cells with the checkboxes turn green when checked.

I'm quite stuck with the rest of it. What formula do I have to enter in cell B2 to let it have the same colour as the cells with the checkboxes. I can't really make it up from your explanation above.

And how do I let the checked checkboxes affect the total amount of money that needs to be paid?

Kind Regards

Michaël

• ###### 8. Re: How do I match a result to the color of certain cells?
Level 7 (29,180 points)

Hi Michael,

The content of B2 ( "BBB" in my last image) has no effect on the fill colour of that cell. The original table has it's fill colour set to 'none', making the table and all of its cells transparent.Here's the original table again, this time with the fill colour and conditionally set fill colour removed from column A, and with an oval shape, filled with a colour gradient placed behind it. Note that the shape can be seen through all cells except those in the Header Row (1) and the Footer Row (11).

The colours are the result of a fill colour (salmon) and a conditional fill colour (green) applied to a second table. In practice, text in the second table is given the same default colour as the fill colour and the same conditional colour as the conditional fill colour. For this illustration below, I've chosen hues that are slightly different to permit seeing the text in each of these cells. Here is the same table shown above, with the oval shape taken away, and the second table shown below the original:

Note that the second table is a single column table, has a Header row, but no footer row, is the same width as the original table, and has rows that are the same height as the corresponding rows in the original table.

The formula on this table, entered into cell A2, and filled down to A10, is shown below:

A2: =Table 1 :: A2

Each cell corresponding to a checked box contains "TRUE"; each corresponding to an unchecked box contains "FALSE".

The Conditional format rule (shown in my post above) sets the fill and text colour to green if the cell contains "TRUE".

When the second table is placed behind the original, the colours show through, and the rows appear to be coloured.

Note that two more people have now confirmed, their rows are coloured, and the total has increased.

The total is calculated in cell C11 by counting the checked ("TRUE" boxes, and multiplying the price per person by that count:

C11: =63.45*COUNTIF(\$A,TRUE)

Regards,

Barry

• ###### 9. Re: How do I match a result to the color of certain cells?
Level 1 (0 points)

Hi Barry

That seems to work just fine!