copy rows from one worksheet to another

Hi Alfeo,

Here are two approaches one robust and one simple.

The simple:

=master::A1

this goes into your summary table pointing at the row you want for that table. You fill it across.

=INDEX(master::B,MATCH(A2,master::A,0), 1,area-index)

This formula will search in the master for the value in A2. I will use the found row to return a value. Fill it across. This will continue to work if the rows in the master are rearranged.

quinn

Hi Alfeo,

Numbers terminology differs from Excel.

A file or a document or a spreadsheet contains

Sheets which contain a

Canvas which contains

Tables

You can have many sheets in a document.

You can have many tables in a sheet.

quinn

Hi Alfeo,

What are you saying about barcodes? Is that something you want to search for? Are they unique yet describe whether an item is a lens, camera, support, etc.? Not every row has one. They do not look useful as an index.

What you need to automatically pull rows into another table is a consistent descriptive. If you had a column for "camera", "lens", "support", etc. you could create an index column to alow you to pull, for instance, any new camera purchase into a camera table.

Another concideration is how you want the items to show up in your summary tables. Is date entered in the master sufficient? Is there another consistent approach across all types that you prefer?

quinn

Hello

If I understand it correctly, you may try the following scheme using barcode ranges (100s, 200s, etc) to indentify categories (camera, accessory, etc). It introduces auxiliary columns L and M in MASTER and L in each summary table.

MASTER (excerpt) A1 BarCode A2 100 A3 101 A4 A5 A6 110 A7 111 A8 A9 200 A10 201 A11 A12 A13 202 A14 L1 L2 =IF(LEN(A2)>0,A2,OFFSET(L$1,ROW()-2,0)) L3 =IF(LEN(A3)>0,A3,OFFSET(L$1,ROW()-2,0)) L4 =IF(LEN(A4)>0,A4,OFFSET(L$1,ROW()-2,0)) L5 =IF(LEN(A5)>0,A5,OFFSET(L$1,ROW()-2,0)) L6 =IF(LEN(A6)>0,A6,OFFSET(L$1,ROW()-2,0)) L7 =IF(LEN(A7)>0,A7,OFFSET(L$1,ROW()-2,0)) L8 =IF(LEN(A8)>0,A8,OFFSET(L$1,ROW()-2,0)) L9 =IF(LEN(A9)>0,A9,OFFSET(L$1,ROW()-2,0)) L10 =IF(LEN(A10)>0,A10,OFFSET(L$1,ROW()-2,0)) L11 =IF(LEN(A11)>0,A11,OFFSET(L$1,ROW()-2,0)) L12 =IF(LEN(A12)>0,A12,OFFSET(L$1,ROW()-2,0)) L13 =IF(LEN(A13)>0,A13,OFFSET(L$1,ROW()-2,0)) L14 =IF(LEN(A14)>0,A14,OFFSET(L$1,ROW()-2,0)) M1 M2 =INT(L2/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L2/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L2/100+1)*100) M3 =INT(L3/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L3/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L3/100+1)*100) M4 =INT(L4/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L4/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L4/100+1)*100) M5 =INT(L5/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L5/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L5/100+1)*100) M6 =INT(L6/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L6/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L6/100+1)*100) M7 =INT(L7/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L7/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L7/100+1)*100) M8 =INT(L8/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L8/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L8/100+1)*100) M9 =INT(L9/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L9/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L9/100+1)*100) M10 =INT(L10/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L10/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L10/100+1)*100) M11 =INT(L11/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L11/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L11/100+1)*100) M12 =INT(L12/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L12/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L12/100+1)*100) M13 =INT(L13/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L13/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L13/100+1)*100) M14 =INT(L14/100)*100&"-"&COUNTIFS(OFFSET(L$1,0,0,ROW(),1),">="&INT(L14/100)*100,OFFSET(L$1,0,0,ROW(),1),"<"&INT(L14/100+1)*100)

CAMERA (excerpt) A1 BarCode A2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) B1 Description B2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) C1 C2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) D1 Serial D2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) E1 Purchase E2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) F1 Replacement F2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) G1 Rate G2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) H1 Date Purchased H2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) I1 Vendor I2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) J1 Part # J2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) K1 Notes K2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(MASTER::$A:$K,$L2,COLUMN())),"",INDEX(MASTER::$A:$K,$L2,COLUMN()))) L1 100 L2 =IFERROR(MATCH(L$1&"-"&ROW()-1,MASTER::M,0),"")

Notes.

Formulae in MASTER::L2:M2 can be filled down.

Formula in CAMERA::L2 can be filled down.

Formula in CAMERA::A2 can be filled down and right across CAMERA::A:K.

CAMERA and ACCESSORY tables are identical except for the value in L1, which specifies the base barcode value for the category.

You need to specify base barcode values in MASTER::A such as 100 in A2 and 200 in A9.

You can hide MASTER::L:M and CAMERA::L etc if you want to.

You can add or remove rows in MASTER. (As far as I can tell with Numbers v2, formulae in MASTER::M:L will be correctly filled in added rows even when inserted in-between.)

Tables are built with Numbers v2.

Good luck,

H

EDIT: replaced screen shot with correct one.

Hello

Couple of comments.

1) You'd better uncheck "use header cell names for reference" in preferences especially when debugging formulae.

2) Numbers containers have following containment hierarchy:

document > sheet > table > range

And the full referencing descriptor in formula is in the form as sheet::table::range. If the table name is unique in document or referent table is in the same sheet as the table where referencing formula resides, you may omit sheet name.

E.g. In my example bulit with Numbers v2, MASTER is a table name in sheet 'Sheet 1' and its full descripter is 'Sheet 1'::MASTER. In your example, CAMERA is a sheet name and 'Updated: Wednesday, April 15, 2015' is a table name and its full descriptor is CAMERA::'Updated: Wednesday, April 15, 2015'.

In other words, mapping table between my references and yours would be:

'Sheet 1'::MASTER => CAMERA::'Updated: Wednesday, April 15, 2015' 'Sheet 1'::CAMERA => 'Alexa SxS Subrental Inventory'::? * I don't know the table name in your latter sheet.

3) You're using wrong cell address $L1, which should have been $L2, in your formula in A2 in a summary table. Also your formula in L2 contains reference error (#REF!). Correct formulae would have been as follows.

Formulae (excerpt) for sheet = Alexa SxS Subrental Inventory table = ? A1 BarCode A2 =IF(LEN($L2)=0,"",IF(ISBLANK(INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L2,COLUMN())),"",INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L2,COLUMN()))) A3 =IF(LEN($L3)=0,"",IF(ISBLANK(INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L3,COLUMN())),"",INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L3,COLUMN()))) A4 =IF(LEN($L4)=0,"",IF(ISBLANK(INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L4,COLUMN())),"",INDEX(CAMERA::'Updated: Wednesday, April 15, 2015'::$A:$K,$L4,COLUMN()))) . . . L1 100 L2 =IFERROR(MATCH(L$1&"-"&ROW()−1,CAMERA::'Updated: Wednesday, April 15, 2015'::M,0),"") L3 =IFERROR(MATCH(L$1&"-"&ROW()−1,CAMERA::'Updated: Wednesday, April 15, 2015'::M,0),"") L4 =IFERROR(MATCH(L$1&"-"&ROW()−1,CAMERA::'Updated: Wednesday, April 15, 2015'::M,0),"")

* Sheet name "CAMERA" may be optional if the table name is unique in document.

* If you check "use header cell names for reference" in preferences, the column reference M in formulae in L will be replaced with header cell name that is Index such as

CAMERA::'Updated: Wednesday, April 15, 2015'::Index

which might be problematic due to name conflict with function name (INDEX) and indeed would be the cause of reference error you're getting. At least it must be quoted such as

CAMERA::'Updated: Wednesday, April 15, 2015'::'Index'

Hope this may help,

H

EDIT: fixed typos