11 Replies Latest reply: Aug 11, 2014 8:51 AM by Weaver158
Weaver158 Level 1 Level 1 (0 points)

I have a Numbers spreadsheet divided into five sheets. I wish to insert a text word “BUY” into cell A1 of sheet 5 providing any two of cells A1 in Sheets 1, 2, 3, and 4 contain the word “BUY”.  Many thanks to anyone who can solve this.


iMac, Mac OS X (10.7.3), 2.7 GHz Intel Core i5 Memory 4 GB
  • Wayne Contello Level 6 Level 6 (15,080 points)

    You can set up like this:

    Screen Shot 2014-08-06 at 9.00.44 AM.png

     

    Sheet 1 :: Table 1 :: A1 = "BUY"

    Sheet 2 :: Table 1 :: A1 = "BUY"

    Sheet 3 :: Table 1 :: A1 = ""

    Sheet 4 :: Table 1 :: A1 = ""

     

     

    Screen Shot 2014-08-06 at 9.00.17 AM.png

    Sheet 5 :: Table 1 :: A1 =IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")

     

    This is shorthand for select cell A1 of Table 1 in Sheet 5 the type (or copy and paste from here) the formula:

    =IF((COUNTIF(Sheet 1::Table 1::A1, "BUY")+COUNTIF(Sheet 2::Table 1::A1, "BUY")+COUNTIF(Sheet 3::Table 1::A1, "BUY")+COUNTIF(Sheet 4::Table 1::A1, "BUY"))≥2, "BUY", "")




  • Weaver158 Level 1 Level 1 (0 points)

    Dear Wayne Contello,

     

    Many thanks for your help - the formula you gave worked with no trouble at all and I will be able to continue with the project I am involved with. I kept getting syntax errors with what I was trying before and your advice has been "just what the doctor ordered"!

     

    Kind regards,

     

    Weaver 158

  • Weaver158 Level 1 Level 1 (0 points)

    Dear Wayne Contello,

     

    Further to my original post I find that if I use the formula specified below I get  #REF! errors for Sheets 3, 4 for some unknown reason the Sheet 5::Table 1 disappeared from Sheet 5 and I was left with just H1074. The following formula was copied and pasted into Sheet 5 Cell J1074:-

     

    =IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::Table 1::F1074, "BUY")+COUNTIF(Sheet 4::Table 1::F1074, "BUY”)+COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "")

     

    The errors are shown as:-

     

    =IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(#REF!, "BUY")+COUNTIF(#REF!, "BUY")+COUNTIF(H1074,"BUY"))≥2, "BUY", "")

     

    Am I right in assuming that  because Cells F1074, (Sheet 3), F1074 (Sheet 4), and H1074 (Sheet 5) are  different Rows to Sheets 1 and 2 which used E3073 and D3073 they are unacceptable to the COUNTIF command?

     

    Is there any way that this problem can be solved or is it necessary to change the overall dimensions of Sheets 3, 4 and 5 so that the correspond with Sheets 1 and 2?

     

    Any suggestions you can make will be very welcome.

     

    Kind regards, 

     

    Weaver 158

  • Wayne Contello Level 6 Level 6 (15,080 points)

    I suggest checking that each of the sheets and tables you reference exist.

     

    Does "Sheet 3" exist?

    Does "Sheet 3" contain a table named "Table 1"?

     

    Same questions for "Sheet 4" and "Table 1"

  • Weaver158 Level 1 Level 1 (0 points)

    Dear Wayne Contello,

     

    Thanks for your quick reply. I have looked at the spreadsheet containing the 5 different sheets and they all appear to be OK. The Table 1 Header lists each of the individual Sheets under their respective numbers along the header bar (Sheet 1, Sheet 2, Sheet 3, Sheet 4 and Sheet 5). Each of these Sheets is large  and covers a lot of price information and I can access each one individually by clicking the appropriate Sheet number in the header bar which is then accented in blue when the Sheet appears on screen.

     

     I have tried writing the formula covering Sheets 1 and 2 only and it works OK. However when it is extended to cover Sheets 3, 4 and 5 I get the results that I have outlined in my previous post.

     

    Any further advice you may be able to offer will be welcomed.

     

    Kind regards, Weaver 158

  • t quinn Level 4 Level 4 (2,135 points)

    Hi Weaver,

     

    If the Sheets::Tables::cells you are trying to reference exist then you can fix your formulas this way:

     

    Click on #Ref!

    navigate to the cell you need to reference, click on it

    repeat till done, hit enter

     

    quinn

  • Weaver158 Level 1 Level 1 (0 points)

    Dear Quinn,

     

    Thanks for your advice which I have followed.  I am sorry to say that it has not produced the result I was hoping for. The resulting formula now reads as follows:-

     

      =IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::'Momentum 95/105'::F1074,, "BUY")+COUNTIF(Momentum Variable::F1074, "BUY")+COUNTIF(H1074,"BUY"))≥2, "BUY", "")

     

    The formula I was hoping would appear is:-

     

      =IF((COUNTIF(Sheet 1::Table 1::E3073, "BUY")+COUNTIF(Sheet 2::Table 1::D3073, "BUY")+COUNTIF(Sheet 3::Table 1::F1074, "BUY")+COUNTIF(Sheet 4::Table 1::F1074, "BUY”)+COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "")

     

    On accepting the incorrect formula the Cell H1074 on Sheet 5 has a red triangle in it which on clicking says “Countif requires 2 arguments but was given 3”.

     

    Any further advice will be welcome.

     

    Kind regards,

     

    Weaver 158.

  • t quinn Level 4 Level 4 (2,135 points)

    Hi Weaver,

     

    The error message is saying that in one of your COUNTIF formulas you have at the least an extra comma. It seems to me (and I am not trying to understand your formulas, just proof read them) that there is an extra comma here:

    COUNTIF(Sheet 3::'Momentum 95/105'::F1074,, "BUY")


    The last part of your formula:

    COUNTIF(H1074,"BUY"))≥2, "BUY", "") is different from

    COUNTIF(Sheet 5::Table 1::H1074,”BUY”))≥2, "BUY", "") because you are entering the formula in Sheet 5::Table 1 and that info is unnessasary.


    If the formula is referencing the wrong tables or the wrong cells I think you may have to take responcibility and go back and click where you actually want. Maybe the table is not the table you think it is. It looks like you are clicking in Tables "  'Momentum 95/105' " (whats with the quotes in the title?) and "Momentum Variable" instead of "Table 1" of those sheets.


    "for some unknown reason the Sheet 5::Table 1 disappeared from Sheet 5 and I was left with just H1074" this does not make sense. You cannot have a cell without a table. You can have a single cell table. Perhaps you are running a filter or have hidden rows/columns.


    It is recomended to stay away from complicated, long, table names, particularly names with many spaces and special characters (i.e. "/") this can invite problems.


    quinn

  • Yellowbox Level 5 Level 5 (6,460 points)

    Hi Weaver,

     

    An extra comma has crept into the formula

     

    Screen Shot 2014-08-10 at 3.33.33 pm.png

    Regards,

    Ian.

     

    Hi quinn, great minds... . Ian.

  • Weaver158 Level 1 Level 1 (0 points)

    Hi Yellowbox,,

     

    Thanks for your timely advice. You will be pleased to hear that everything is now just fine and my project is going well.

     

    Kind regards,

     

    Weaver 158

  • Weaver158 Level 1 Level 1 (0 points)

    Dear Quinn,

     

    Your suggestion sorted things out and everything is now working fine. Thanks for your advice which I appreciate.

     

    Kind regards,

     

    Weaver 158