Using a function/formula for copying text

[McKenna]

I have a spreadsheet that contains information on work performed that day

1A DATE
1B WORK ORDER NUMBER
1C TECHNICIAN
1D WORK PERFORMED

I would like to know how based on column 1B to get all the information
entered on Line 1 copied to another spreadsheet. I am sure that the IF
function is used. I have managed to get 1B WORK ORDER # to copy to the other
spreadsheet but have spent hours trying to get the remaining cells to
trasfer. Doing this would allow me to enter infomation on a main spreadsheet
and then it would transfer to a seperate spreadsheet dedicated to that work
order number.

 

[Max]

There's a formulas driven option available to serve your exact needs ..

Take away this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top,
w/o any intervening blank lines. Ditto for lines with "CA", "NV", etc which
will be copied into their respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg just
formulate one child sheet for "NY", dress it up nicely, then just make copies
of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be the one housing your WO codes: 1B, 1C, 1D
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[McKenna]

Thanks for the reply,

I have downloaded the spreadsheet and this is exactly what I am looking for.
I tried changing the state name to my work order number and then changed the
spread sheet for that state to the same name. Unfortunently the info did not
copy over. I tried adding a new sheet by copying an old one and changed the
name to my RO number, but that also didn't work. I did not have any problem
adding columns to be copied. Could you possible tell me what I am doing wrong?

Thanks

 

[Max]

Can you upload your sample file using a free filehost,
then post a link to it here?

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[Max]

I'm using xl2003. Pl upload only .xls
(I'm unable to read .xlsx)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---

 

[Max]

This'll get you going smoothly ...

Here's the implementated solution in your sample:
http://freefilehosting.net/download/3kg7m
AutoCopy by WO into its own sht.xls

Btw, pl press the Yes button below, from where you're reading this

The construct:
In the parent sheet, ie: MASTER
Key col is col A (WO numbers)
List the unique WO numbers in K1 across, in any order, eg: 123456, 234567, etc
In K2: =IF(OR($A2="",K$1=""),"",IF($A2=K$1,ROW(),""))
Copy across/fill down to cover max expected extent of source data in col A

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 the 1st child sheet, named: 123456
In A2:
=IF(ROWS($1:1)>COUNT(OFFSET(MASTER!$J$2:$J$1000,,MATCH(WSN+0,MASTER!$K$1:$IV$1,0))),""
INDEX(MASTER!A$2:A$1000,MATCH(SMALL(OFFSET(MASTER!$J$2:$J$1000,,MATCH(WSN+0,MASTER!$K$1:$IV$1,0)),ROWS($1:1)),
OFFSET(MASTER!$J$2:$J$1000,,MATCH(WSN+0,MASTER!$K$1:$IV$1,0)),0)))

Copy A2 across to G2, fill down to cover max expected number of lines per
any WO.
(Adapt the ranges to suit the max extents in MASTER)

Then just make copies of the child sheet, rename these as the other WO
numbers.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[Max]

Some explanations ..

.. why wouldn't it work if I made uniform name changes?

The subtleness is in the way the data is returned. "Numeric" sheetnames (eg:
123456) returned by the defined range WSN are text numbers, not real numbers.
Hence, for it to match with the real numbers listed in K1 across in the
parent sheet, we can use, eg: WSN+0, where the "+0" will coerce the text
number to a real number w/o impacting the underlying numeric value.

The other key change made in the solution for your instance was the
trade-off to use fixed ranges eg: MASTER!A$2:A$1000 & MASTER!$J$2:$J$1000,
instead of entire col ranges. This precaution eliminates the possibility of
the real numbers in K1 across in the parent sheet interfering with the
arbitrary row numbers returned within the criteria range below. This change
is not required (we can use the neater entire col refs) if the child
sheetnames are all non-numeric in nature.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---