Excel spreadsheet

Discussion in 'Mac Apps and Mac App Store' started by Telp, May 7, 2008.

  1. Telp macrumors 68040

    Telp

    Joined:
    Feb 6, 2007
    #1
    I'm trying to figure out the formula for a cell that calls for a Y (yes) or N (no) entry (sheet 2) so that it ports over to the overview sheet (sheet 1) as a numeric value .

    In other words, if a Y is placed in cell H3 on sheet #2, how can I get it to show up on sheet #1, cell E8, as a numeric value.

    ex. 7 people answered yes to the first question on sheet #2...

    Thanks!

    Telp
     
  2. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #2
    If it's just one cell, you can use a straight if for this purpose...something like

    if(Sheet2!H3="Y", 1, 0)

    (syntax might be slightly off)

    If there is a range, countif is probably a better option.

    countif(Sheet2!H3:H40, "Y")

    would produce the number of Y's in that range.
     
  3. Telp thread starter macrumors 68040

    Telp

    Joined:
    Feb 6, 2007
    #3
    Perfect! That is exactly what I needed. Thank you very much!


    -Telp
     
  4. Slumbercub macrumors regular

    Joined:
    Jul 12, 2006
    Location:
    Gloucester, England
    #4
    Just a little tip that may add a bit of elegance to your spreadsheet. Instead of entering 'Y' you may want to use a checkbox (inspector, cell inspector, drop down box). If it is empty it is No if ticked it is Yes. Just point the formula condition of the IF function to the cell with the checkbox and then use 1 for [if-true] and 0 for [if-false].

    :)
     
  5. HansHans macrumors newbie

    Joined:
    May 23, 2008
    #5
    Hi Slumbercup,

    very interesting comment. Just one question: how do I bring up the inspector you mentioned in Excel? I can't find it anywhere.

    Thanks for your help.

    Hans
     
  6. Slumbercub macrumors regular

    Joined:
    Jul 12, 2006
    Location:
    Gloucester, England
    #6
    No idea I am afraid, the way I described it was using Numbers but obviously Excel has the same functionality.
     
  7. HansHans macrumors newbie

    Joined:
    May 23, 2008
    #7
    No, it hasn't, I'm afraid. The Windows Excel has a "design mode" that let's you create command buttons, listboxes, dropdown boxes, checkboxes etc. and I can't find that in Excel for Mac.
     
  8. HansHans macrumors newbie

    Joined:
    May 23, 2008
    #8
    Just wanted to say: I found it at last. A post on the "micosoft.public.mac.office.excel" newsgroup hinted me to it.

    The controls can befound here: On the menu click "view-->toolbars-->forms".

    :)
     

Share This Page