Automatically copy data from one worksheet to another

  • Thread starter Thread starter karstens
  • Start date Start date
K

karstens

I posted this before with no response, so I am going to try it again.

I have data on one sheet with some of the following columns
Workorder ~ Date ~ Description ~ Quantity ~ Amount ~ G/L Account
NU-702-06 ~ 6/6/06 ~ Menards ~ 0 ~ $150.00 ~ 2232

Then I have 100+ tabs for each workorder. Each tab contains a form that
has information on it regarding the invoices that pertain to that
workorder.
What I would like to do, is enter all the invoices on that first sheet
and have each tab automatically update. So for instance, in the example
above, I would like to have that same information show up on the form
for tab 702.

Does that make sense? Currently I have the first sheet show all the
invoices and then I copy and paste each one into the individual
workorder sheets. I am sure there is a better way to do this, I just
can't seem to figure it out. Thank you for your help in advance!
 
Hi Karstens,

I understand what you need, but this will require VBA coding.

With just Excel formulas and functions you need to enter the functions
all over again and again in each of your tabs.

Thanks

Shail
 
Here's a set up which delivers it using non-array formulas ..

A sample implementation is available at:
http://www.savefile.com/files/7761089
AutoCopy Data To Own Sheet by WorkOrder.xls

In sheet: X (the "master")

Assume data in cols A to F, data in row2 down,
with the key col = col A (Workorder)

Format K1:IV1 as Text. Then enter the workorders (702, 703, etc) -- ie the
tabnames -- in K1 across (workorders can be entered in any order in K1 across)

Put in K2:
=IF($A2="","",IF(MID($A2,SEARCH("-",$A2)+1,SEARCH("-",$A2,SEARCH("-",$A2)+1)-(SEARCH("-",$A2)+1))=K$1,ROW(),""))
Copy across as far as required and fill down to say, row200 to cover the max
expected extent of data in X

Note: I've assumed that your "workorders" listed in the key col A in X are
all in the same representative format, eg: NU-702-06, NU-1702-11, etc where
the workorder# is the number bounded by 2 dashes "-", viz.: 702, 1702, etc.
The number of digits comprising the workorder# housed within the 2 dashes is
immaterial, it could be 3 or 4 or 5 digits, etc as the MID(...) will extract
it correctly.

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named as say: 702

With the same col headers pasted into A1:F1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(X!$J:$J,,MATCH(WSN,X!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(X!A:A,MATCH(SMALL(OFFSET(X!$J:$J,,MATCH(WSN,X!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(X!$J:$J,,MATCH(WSN,X!$K$1:$IV$1,0)),0)))

Copy A2 across to F2, fill down to say, F10
(copy down by the smallest possible range sufficient to cover the max
expected number of lines for any workorder. Here, I've assumed that 9 rows
(rows 2 to 10) is sufficient) Format col B as dates, col E as currency

Cols A to F will return only the lines for workorder: 702 from X with all
lines neatly bunched at the top

Now, we just need to make a copy of the sheet: 702, rename it as: 703, and
we'd get the results for the workorder: 703. Repeat the copy > rename sheet
process to get the rest of the workorder sheets (just a one-time job). Adapt
to suit ..

---
 
Back
Top