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
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:
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:
- 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)))
- 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. - 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. - 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:
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
Posted on Sep 23, 2016 6:21 PM

