Skip navigation

Programming a Cell

382 Views 10 Replies Latest reply: May 24, 2012 6:36 PM by Barry RSS
centcougarxc1630 Calculating status...
Currently Being Moderated
May 22, 2012 7:06 AM

How do you make a cell turn a certain color based on its text?  For example, if I typed in "Hey", the box would turn blue because it is programmed to do so.

 

The "Hey" would also still stay in the text box, so it would be "Hey" with a blue background.

  • Wayne Contello Level 6 Level 6 (12,610 points)
    Currently Being Moderated
    May 22, 2012 7:19 AM (in response to centcougarxc1630)

    You use conditional formatting.  select the cell(s) you want to format, then select the menu item "Format > Show Conditional Format Rules"

    Screen Shot 2012-05-22 at 9.16.22 AM.png

     

    Then select the type of condition (for your case "Text is") and enter the string to look for, then how to adjust the formatting in when the condition is true.

     

    Screen Shot 2012-05-22 at 9.18.05 AM.png

  • Wayne Contello Level 6 Level 6 (12,610 points)
    Currently Being Moderated
    May 22, 2012 8:03 AM (in response to centcougarxc1630)

    You can do something like:

    Screen Shot 2012-05-22 at 10.00.01 AM.png

    This uses two tables: one for your data and another to summarize the data.  This allows you to move the summary table as needed.

     

    The formulas in the Summary table:

    B2=SUMIF(Earnings :: A, "="&A2, Earnings :: B)

    select B2 and fill down.   This is shorts hand for in cell B2 and the the text "=SUMIF(Earnings :: A, "="&A2, Earnings :: B)" without the double quotes.

     

    to fill down select B2, then grab (click and hold) the little circle at the bottom right of the selected call, then drag down.  Enter the leagues in the column A to get a summary for that league.

  • Wayne Contello Level 6 Level 6 (12,610 points)
    Currently Being Moderated
    May 22, 2012 8:37 AM (in response to centcougarxc1630)

    one way is to add the two individual summaries together, the other is to give them a common name (but then you lose the separate information):

     

    Try this way:

    Screen Shot 2012-05-22 at 10.36.32 AM.png

     

    I made a new table called "Summary2" and only include CNS and NSS, then I added footer row where I sum the two together):

    B4=SUM(B)

  • Wayne Contello Level 6 Level 6 (12,610 points)
    Currently Being Moderated
    May 22, 2012 8:58 AM (in response to centcougarxc1630)

    Add a new column to your data entry table (you can hide later):

     

    Screen Shot 2012-05-22 at 10.56.36 AM.png

    C2=OR(A2="CNS", A2="NSS")

    select C2 and fill down

     

    Screen Shot 2012-05-22 at 10.55.41 AM.png

     

     

    In the summary table:

    B2=SUMIF(Earnings :: C, "=TRUE", Earnings :: B)

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 22, 2012 4:02 PM (in response to centcougarxc1630)

    centcougarxc1630 wrote:

     

    My code right now looks like this:

    =SUMIF(Table 1 :: League, "="&"MRSL", Table 1 :: Pay)

     

    In this case, I want MRSL and MWSL added together, so can I add MWSL somewhere in this code so that it recognizes both of them?  I tried something like this...

    You could do exactly what you've described above: "I want MRSL and MWSL added together." The first part of the formula below calculates the sum for MRSL,the second part calculates the sum for MWSL, the addition operator ( + ) adds the two results.

     

    =SUMIF(Table 1 :: League, "=MRSL", Table 1 :: Pay)+SUMIF(Table 1 :: League, "=MWSL", Table 1 :: Pay)

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 24, 2012 6:36 PM (in response to centcougarxc1630)

    Hi c,

     

    This introduces a third topic into a thread that has the heading "programming a cell" and asked about formatting a cell based on its content. Looks like time to start a new question.

     

    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.