10 Replies Latest reply: May 24, 2012 6:36 PM by Barry
centcougarxc1630 Level 1 Level 1 (0 points)

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.

  • 1. Re: Programming a Cell
    Wayne Contello Level 6 Level 6 (13,620 points)

    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

  • 2. Re: Programming a Cell
    centcougarxc1630 Level 1 Level 1 (0 points)

    Great, thank you!  I also have another question that you could maybe answer.

     

    So I am keeping track of earnings for a refereing job that I have, and I work with several different leagues.  Lets say if I input a league, call in "CNS" in A1, but in D1 I have how much I made.  And this would continue downwards, with several different leagues.  At the bottom of the spreadsheet I total how much I made in each league.  Is there some way that I can automatically do this?  So if I type in the league "CNS" it automatically looks in the "money" column to the right and keeps a running total of how much I made from the league "CNS" at the bottom of the spreadsheet.  Does that make sense?  It would be nice to see the total in one single cell at the bottom of the sheet for each particular league.

  • 3. Re: Programming a Cell
    Wayne Contello Level 6 Level 6 (13,620 points)

    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.

  • 4. Re: Programming a Cell
    centcougarxc1630 Level 1 Level 1 (0 points)

    How do I do a correct "or" statement?  Like if its "CNS" or "NSS" import the dollar value.

  • 5. Re: Programming a Cell
    Wayne Contello Level 6 Level 6 (13,620 points)

    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)

  • 6. Re: Programming a Cell
    centcougarxc1630 Level 1 Level 1 (0 points)

    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...

     

    =SUMIF(Table 1 :: League, "="&"MRSL"&"MWSL", Table 1 :: Pay), but that does not work.  I need some kind of or statement so it knows that both are okay.

  • 7. Re: Programming a Cell
    Wayne Contello Level 6 Level 6 (13,620 points)

    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)

  • 8. Re: Programming a Cell
    Barry Level 7 Level 7 (29,180 points)

    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

  • 9. Re: Programming a Cell
    centcougarxc1630 Level 1 Level 1 (0 points)

    What if I want to do something like this, where I want to input the pay amount in the top table based on what age and league it is.  So it will look in the second table for the correct dollar amount.  If I have ages U14G and U14B can it recognize that both of those are categorized under the U14 pay category?

     

     


  • 10. Re: Programming a Cell
    Barry Level 7 Level 7 (29,180 points)

    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