Skip navigation

How do I match a result to the color of certain cells?

636 Views 9 Replies Latest reply: Apr 30, 2013 9:16 AM by Michaël D RSS
Michaël D Calculating status...
Currently Being Moderated
Apr 17, 2013 11:15 AM

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)
  • Barry Level 7 Level 7 (29,095 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:

    Picture 4.png

     

    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

  • Jerrold Green1 Level 7 Level 7 (28,190 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

  • Barry Level 7 Level 7 (29,095 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.

    Picture 8.png

    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.

    Picture 9.png

    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.

    Picture 10.png

    Regards,

    Barry

  • Hiroto Level 5 Level 5 (4,810 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

  • Hiroto Level 5 Level 5 (4,810 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

  • Barry Level 7 Level 7 (29,095 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).

    Picture 11.png

    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:

    Picture 13.png

    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.

    Picture 14.png

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.