PhilH1979

Q: Numbers Help

HI,

 

I have numerous sheets that i want to create a summary page for

 

sheet 1-- Fixings

sheet 2-- cable

 

both have 2 columns

A--Quantity

B--Description

 

when i add a value to column "A" i would like both Column A & B to appear onto my summary page

 

Hope that make sense!

 

Thanks Phil

iMac, iOS 10

Posted on Sep 23, 2016 7:33 AM

Close

Q: Numbers Help

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Sep 23, 2016 9:13 AM in response to PhilH1979
    Level 6 (10,796 points)
    Mac OS X
    Sep 23, 2016 9:13 AM in response to PhilH1979

    Can you post a screenshot?

     

    BTW, Numbers does not have "pages". A Numbers document contains sheets (tabs) which contain tables (and text boxes and shapes).

     

    SG

  • by Barry,Solvedanswer

    Barry Barry Sep 23, 2016 6:21 PM in response to PhilH1979
    Level 7 (32,697 points)
    iWork
    Sep 23, 2016 6:21 PM in response to PhilH1979

    Hi Phil,

     

    My interpretation:

     

    You have two tables, one named Fixings, the other named Cable. The tables may be on the same sheet, or may be on different sheets, but both are in the same document (file).

     

    Each of these tables has two columns (A and B)

    Column A is labeled "Quantity"

    Column B is labeled "Description"

     

    You want to create a third table (on a separate sheet in the same document).

    This table will also have two columns, with the same pair of labels as above.

     

    When you enter a quantity into a cell on column A of Fixings, you want that quantity, and the description in the same row of column B to appear on Summary.

     

    Here's a possible solution:

    Screen Shot 2016-09-23 at 5.43.34 PM.png

    Setup:

    Fixings and Cable each need one column (C) added to contain an index marking the rows that are to be copied into Summary. I've used a light grey font colour on these to indicate the columns may (should) be hidden. They are needed by the formula on Summary, but do not need to be seen by the user.

     

    C2 on each of these tables contains the formula below, which is then filled down to all cells in column C of its table. The two formulas are the same.

     

    Fittings::C2: =IF(A>0,MAX(C$1:C1)+1,"")

      Cable::C2: =IF(A>0,MAX(C$1:C1)+1,"")

     

    Cable::C1 contains this formula, which returns the maximum index value in column C of Fixtures:

     

      Cable::C1: =MAX(Fixings::C)

     

    Summary contains a single formula, entered in A2, then filled right to column B and down to the last row of the table:

     

    Summary::A2: =IF(ROW()−1>MAX(Cable::$C),"",IF(ROW()−1>MAX(Fixings::$C),LOOKUP(ROW()−1,Cable: :$C,Cable::A),LOOKUP(ROW()−1,Fixings::$C,Fixings::A)))

     

    Stacking the sections of the formula make it a bit easier to read:

     

    1. IF(ROW()−1>MAX(Cable::$C),"",
    2. IF(ROW()−1>MAX(Fixings::$C),LOOKUP(ROW()−1,Cable::$C,Cable::A),
    3. LOOKUP(ROW()−1,Fixings::$C,Fixings::A)))

     

    1. If the row number minus 1 is larger than the maximum index number in column C of Cable, all items in both tables have been transferred. Return a null string ( "" ) (and exit).
      if not, go to the next section.

    2. If the row number minus 1 is larger than the maximum index number in column C of Fixings, all items in Fixings have been transferred. Lookup row-1 in column C of Cable, and return the content of the same row of column A. (and exit).
      If not, go to the next section.

    3. Lookup row-1 in column C of Fixings, and return the content of the same row of column A. (and exit).

     

    If your tables are named to match my examples, the formulas may be copied from here and pasted into the Formula editor opened in the specified cell for each.

     

    Select all of the formula after the = sign, copy. Select the cell where it is to go, type an = to open the editor, then paste.

     

    Here is an image of the long formula as it should appear in the formula editor:

    Screen Shot 2016-09-23 at 6.19.13 PM.png

    I've adjusted the size and shape of the editor box to stack the formula parts in a similar manner to the one above.

     

    The three tables here are all on the same sheet of the same document. Because each has a name that is distinct within the document, the formulas used will remain the same if the tables are moved to separate sheets.

     

    The summary table here is the same length as the two data tables, Fixings and Cable. In practice, the Summary table needs to have enough rows to accomodate the largest number of rows to be copied.

     

    Set up as shown, Summary will list the entries from the Fixtures table first, then the entries from the Cable table.

     

    Regards,

    Barry

  • by PhilH1979,

    PhilH1979 PhilH1979 Sep 23, 2016 11:53 PM in response to PhilH1979
    Level 1 (4 points)
    iWork
    Sep 23, 2016 11:53 PM in response to PhilH1979

    Thanks Barry,

     

    I shall give it a go and let you know you know how I get on.

     

    Many thanks for your time.

     

    Phil