If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a function/formula for copying text
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. |
#2
|
|||
|
|||
Using a function/formula for copying text
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" wrote: 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. |
#3
|
|||
|
|||
Using a function/formula for copying text
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" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Using a function/formula for copying text
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 --- "McKenna" wrote in message ... 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 |
#5
|
|||
|
|||
Using a function/formula for copying text
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 --- |
#6
|
|||
|
|||
Using a function/formula for copying text
Thanks for the reply
Here you go, out of curiosity, why wouldn't it work if I made uniform name changes? http://freefilehosting.net/download/3kg76 "Max" wrote: 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 --- "McKenna" wrote in message ... 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 |
#7
|
|||
|
|||
Using a function/formula for copying text
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,,M ATCH(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)),RO WS($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 --- "McKenna" wrote: Thanks for the reply Here you go, out of curiosity, why wouldn't it work if I made uniform name changes? http://freefilehosting.net/download/3kg76 |
#8
|
|||
|
|||
Using a function/formula for copying text
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 --- |
Thread Tools | |
Display Modes | |
|
|