Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
I use excel as a production program for our manufacturing company but
currently move everything over manually. When a job is quoted it gets inputted in as follows; Date/quote #/Name/Accepted/Delivery Date. When the job is accepted we type 'YES' under accpeted and put in an estimated delivery date. When this is done I want that row to move to the month of the delivery date in another sheet. Can anyone help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Here's a formulas driven model which delivers it ...
Illustrated in this sample: http://freefilehosting.net/download/4021h AutoCopy to Month Sheet.xls In the source ("parent") sheet: x, data is assumed in row2 down, where key cols are col D ("Yes"), and col E (est delivery dates - these are real dates) List the MthYr as text* in G1 across, eg: Sep08, Oct08, etc. *Either enter a leading apostrophe eg: 'Sep08, or preformat G1 across as TEXT In G2: =IF($D2<"Yes","",IF(TEXT($E2,"mmmyy")=G$1,ROW()," ")) Copy G2 across/fill down to cover max expected extent of source data in cols D & E 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. Then in the 1st "child" sheet (a new sheet), name it as the MthYr, say: Sep08 Paste the col headers from x into A1:E1 Place in A2: =IF(ROWS($1:1)COUNT(OFFSET(x!$F:$F,,MATCH(TRUE,IN DEX(TEXT(x!$G$1:$K$1,"mmmyy")=WSN,),))),"",INDEX(x !A:A,MATCH(SMALL(OFFSET(x!$F:$F,,MATCH(TRUE,INDEX( TEXT(x!$G$1:$K$1,"mmmyy")=WSN,),)),ROWS($1:1)),OFF SET(x!$F:$F,,MATCH(TRUE,INDEX(TEXT(x!$G$1:$K$1,"mm myy")=WSN,),)),0))) Copy A2 across to E2, fill down to cover max expected number of lines per any delivery MthYr, say down to row50? The source lines for Sep08 which satisfy the criteria will be autocopied from x and appear neatly bunched at the top. Dress this child sheet up to taste. Then just make copies of this sheet: Sep08, & rename these as the other MthYr, viz: Oct08, Nov08, etc. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Kingdazza" wrote: I use excel as a production program for our manufacturing company but currently move everything over manually. When a job is quoted it gets inputted in as follows; Date/quote #/Name/Accepted/Delivery Date. When the job is accepted we type 'YES' under accpeted and put in an estimated delivery date. When this is done I want that row to move to the month of the delivery date in another sheet. Can anyone help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Here's a simpler version for the earlier extract formula in the child sheet,
which works just as well, and also extends it all the way to cover col IV in the source sheet: x In the child sheet: Sep08 Place in A2: =IF(ROWS($1:1)COUNT(OFFSET(x!$F:$F,,MATCH(WSN,x!$ G$1:$IV$1,0))),"",INDEX(x!A:A,MATCH(SMALL(OFFSET(x !$F:$F,,MATCH(WSN,x!$G$1:$IV$1,0)),ROWS($1:1)),OFF SET(x!$F:$F,,MATCH(WSN,x!$G$1:$IV$1,0)),0))) Copy A2 across to E2, fill down to cover max expected number of lines per any delivery MthYr ... (Make copies of the child n rename the sheets as before) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Wow Max that looks great, it's not quite what I'm after but it'son the write
track. I have uploaded acopy of the template on the linkyou sent through,you will get a better idea of what I mean. Each month has a different worksheet but they all need to be talking to each other.If there is a better way I am open to suggestions. Thanks Darren. "Max" wrote: Here's a simpler version for the earlier extract formula in the child sheet, which works just as well, and also extends it all the way to cover col IV in the source sheet: x In the child sheet: Sep08 Place in A2: =IF(ROWS($1:1)COUNT(OFFSET(x!$F:$F,,MATCH(WSN,x!$ G$1:$IV$1,0))),"",INDEX(x!A:A,MATCH(SMALL(OFFSET(x !$F:$F,,MATCH(WSN,x!$G$1:$IV$1,0)),ROWS($1:1)),OFF SET(x!$F:$F,,MATCH(WSN,x!$G$1:$IV$1,0)),0))) Copy A2 across to E2, fill down to cover max expected number of lines per any delivery MthYr ... (Make copies of the child n rename the sheets as before) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
.. I have uploaded a copy of the template ...
Link? You forgot to post the link to your sample in your reply P/s: I might not have another suggestion for you. Thought the earlier effort was quite ok, but ... it's not good enough in your eyes, it seems. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100, Files:360, Subscribers:56 xdemechanik --- "Kingdazza" wrote in message ... Wow Max that looks great, it's not quite what I'm after but it's on the write track. I have uploaded a copy of the template on the link you sent through,you will get a better idea of what I mean. Each month has a different worksheet but they all need to be talking to each other.If there is a better way I am open to suggestions. Thanks Darren. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Max I really do apologies I didn't mean to offend you, it really wrote a lot
worse than I meant. What you have done is more than I had expected, I really do appreciate it. I didn't realise I needed to copy the link (am still not very computer literate) so if you want please take a look at the following; http://freefilehosting.net/download/403eh I'm not sure but will it make it harder because we have each month in a different workbook? If you don't want to look at it I do understand, but please accept my apologies. Thanks again Darren "Max" wrote: .. I have uploaded a copy of the template ... Link? You forgot to post the link to your sample in your reply P/s: I might not have another suggestion for you. Thought the earlier effort was quite ok, but ... it's not good enough in your eyes, it seems. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100, Files:360, Subscribers:56 xdemechanik --- "Kingdazza" wrote in message ... Wow Max that looks great, it's not quite what I'm after but it's on the write track. I have uploaded a copy of the template on the link you sent through,you will get a better idea of what I mean. Each month has a different worksheet but they all need to be talking to each other.If there is a better way I am open to suggestions. Thanks Darren. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
.. What you have done is more than I had expected, I really do appreciate it.
Then pl take a moment to press the "Yes" button (like the one below) in all responses which help directly or indirectly answer your queries I'm not sure but will it make it harder because we have each month in a different workbook? Definitely so. In simple terms, in this order, easiest to difficult: 1. Frame it up in the same sheet in adjacent cols to the right of data 2. Frame it up in another sheet in the same book 3. Frame it up in another sheet in another book The "it" would be the formulas to derive intermediate or final results I'll take a look at your sample later I'll post back here a closure for you, irrespective -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Here's the implemented template:
http://freefilehosting.net/download/404am Quote Log to MthYr Production Template.xls In your source ("parent") sheet: Quote Log, data is in row3 down, where key cols are col F ("Yes" indications under "Accepted" col), and col J (the est delivery dates - these are real dates) List the MthYr as text* in Q2 across, eg: Jan08, Feb08, etc *Either enter a leading apostrophe eg: 'Jan08, or preformat Q2 across as TEXT Leave Q1:IV1 empty In Q3: =IF($F3<"yes","",IF(TEXT($J3,"mmmyy")=Q$2,ROW()," ")) Copy Q3 across/fill down to cover max expected extent of source data in cols F & J Extra: You have a "Total Hours" col in col I, which figs are to be extracted from the respective mthyr sheets for the particular quote# Place in I3, copy down: =IF(J3="","",INDEX(INDIRECT("'"&TEXT(J3,"mmmyy")&" '!I:I"),MATCH(B3,INDIRECT("'"&TEXT(J3,"mmmyy")&"'! B:B"),0))) Create this defined name WSN 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. Then in your 1st "child" sheet (ie your template sheet for the monthly production), rename it as the 1st MthYr, say: Jan08 Place in B3: =IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0))),"", INDEX('Quote Log'!B:B,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),0))) Copy B3 across to H3, fill down to cover max expected number of lines per any delivery MthYr, say down to row50? You have your own formula in I3 down: =SUM(K3:P3) to compute "Total Hours" from other inputs in adjacent cols to the right Place in J3: =IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0))),"", INDEX('Quote Log'!J:J,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),0))) Copy J3 down similarly as for B3:H3 Extra: To create a dynamic title for the child sheet in the top row which takes in the sheetname automatically, Place in A1: ="Monthly Production: "&WSN The source lines for Jan08 which satisfy the criteria will be autocopied from sheet: Quote Log and appear neatly bunched at the top. Dress this child sheet up to taste. Then just make copies of it & rename these as the other MthYr, viz: Feb08, Mar08, etc. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Hi Max,
Fantastic what you have done, again it's more than I expected. I am doing some fine tuning with it and will send you the link when it's done so you can have a look. Thanks again Max I really appreciate your help on this. Darren. "Max" wrote: Here's the implemented template: http://freefilehosting.net/download/404am Quote Log to MthYr Production Template.xls In your source ("parent") sheet: Quote Log, data is in row3 down, where key cols are col F ("Yes" indications under "Accepted" col), and col J (the est delivery dates - these are real dates) List the MthYr as text* in Q2 across, eg: Jan08, Feb08, etc *Either enter a leading apostrophe eg: 'Jan08, or preformat Q2 across as TEXT Leave Q1:IV1 empty In Q3: =IF($F3<"yes","",IF(TEXT($J3,"mmmyy")=Q$2,ROW()," ")) Copy Q3 across/fill down to cover max expected extent of source data in cols F & J Extra: You have a "Total Hours" col in col I, which figs are to be extracted from the respective mthyr sheets for the particular quote# Place in I3, copy down: =IF(J3="","",INDEX(INDIRECT("'"&TEXT(J3,"mmmyy")&" '!I:I"),MATCH(B3,INDIRECT("'"&TEXT(J3,"mmmyy")&"'! B:B"),0))) Create this defined name WSN 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. Then in your 1st "child" sheet (ie your template sheet for the monthly production), rename it as the 1st MthYr, say: Jan08 Place in B3: =IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0))),"", INDEX('Quote Log'!B:B,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),0))) Copy B3 across to H3, fill down to cover max expected number of lines per any delivery MthYr, say down to row50? You have your own formula in I3 down: =SUM(K3:P3) to compute "Total Hours" from other inputs in adjacent cols to the right Place in J3: =IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0))),"", INDEX('Quote Log'!J:J,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote Log'!$Q$2:$IV$2,0)),0))) Copy J3 down similarly as for B3:H3 Extra: To create a dynamic title for the child sheet in the top row which takes in the sheetname automatically, Place in A1: ="Monthly Production: "&WSN The source lines for Jan08 which satisfy the criteria will be autocopied from sheet: Quote Log and appear neatly bunched at the top. Dress this child sheet up to taste. Then just make copies of it & rename these as the other MthYr, viz: Feb08, Mar08, etc. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Here's a way to auto-distribute the top range lines in each monthyr sheet
into the respective week ranges below by each week's date range An example implemented into your sample he http://freefilehosting.net/download/40fjk AutoDistribute By Date Range.xls In a typical monthyr sheet: Jan08, The startdates / enddates for each production week need to be placed in 2 separate cells, eg for the 1st production week, Startdate in D89, Enddate in G49. Repeat likewise for all the production weeks that you have in Jan08. (Keep it simple, avoid using text strings in single cells such as: 01-04Jan08. It's tough to work with these kind of strings as lookup values) Then at the top of the sheet, create the criteria range like this: Manually link each week's startdates in U2 across, enddates in U1 across Then in U3: =IF($F3<"Yes","",IF(AND($M3=U$2,$M3<=U$1),ROWS($ 1:1),"")) Copy U3 across/fill down. The AND check in the expression will flag within the appropriate "week" col based on the actual delivery dates in the key col M (where these dates lie) Then below, within say, the range for the 1st week: 1 Jan 08 to 4 Jan 08 Place in the top left cell A91: =IF(ROWS($1:1)COUNT(OFFSET($T$3:$T$66,,MATCH($D$8 9,$U$2:$Y$2,0))),"",INDEX(A$3:A$66,SMALL(OFFSET($T $3:$T$66,,MATCH($D$89,$U$2:$Y$2,0)),ROWS($1:1)))) Copy A91 across (skipping col I which contains your summation formula), format the dates as required, fill down. This will auto-list the lines from the range above (rows 3-66) which fall within the 1st week's date range, neatly packed at the top. The OFFSET formula will grab the correct "week" col within the criteria range by matching the lookup date in D89 (the startdate) with the startdates in U2 across. Repeat likewise for the next range (2nd week) Place in the top left cell A115: =IF(ROWS($1:1)COUNT(OFFSET($T$3:$T$66,,MATCH($D$1 13,$U$2:$Y$2,0))),"",INDEX(A$3:A$66,SMALL(OFFSET($ T$3:$T$66,,MATCH($D$113,$U$2:$Y$2,0)),ROWS($1:1))) ) The above is essentially the same point formula as for the 1st week above, except that it now points to the 2nd week's start date in D113 (instead of D89). Copy across/fill down. Repeat likewise for all other production week ranges. P/s: Do not post your email in the newsgroups -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Typo he
.. for the 1st production week, Startdate in D89, Enddate in G49. should read: ... Enddate in G89 -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600, Files:362, Subscribers:60 xdemechanik --- |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Hi Max,
We have it working perfectly, everyone here is blown away with what you have done. I want to beable to sort the jobs by 'Actual Delivery Date' in the 'Monthly Schedule' but when I hit the sort button it only moves the row but not the information in it, ie: if I add a comment in the cell that will move but information in the cell stays where it is? Any suggesttions? What's good about having the actua delvey ates in order is that the weekly changes with it which is what I want. Thanks again, is there any way we can give yoou smething for what you have done? Send me an email with your address or something. Darren. "Max" wrote: Typo he .. for the 1st production week, Startdate in D89, Enddate in G49. should read: ... Enddate in G89 -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600, Files:362, Subscribers:60 xdemechanik --- |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
We have it working perfectly ..
Welcome, good to hear that. I want to be able to sort the jobs by 'Actual Delivery Date' in the 'Monthly Schedule' but when I hit the sort button it only moves the row but not the information in it, ie: if I add a comment in the cell that will move but information in the cell stays where it is? Any suggestions? What's good about having the actua delivery dates in order is that the weekly changes with it which is what I want. It won't sort that way because the extract formulas will still evaluate/return independently regardless. To get it to sort, you would need to freeze it (the formulated lines) as values prior to sorting, but that will then negate the modelled dynamics. For what its worth, there is however scope to tweak it to auto-sort in this manner (albeit I'm not sure if it'll fit in with your desired data/process flows): Implemented here for easy reference: http://freefilehosting.net/download/40h9l AutoDistr by Date Range n AutoSort by Date.xls (a) To get the extracted source lines from Quote Log to appear in auto-chrono, ie in ascending order by Est Delivery dates in Jan08's top range part: Make these tweaks: In Quote Log's U4, filled across/down: =IF($G4<"yes","",IF(TEXT($K4,"mmmyy")=U$2,K4+ROW( )/10^10,"")) Then In Jan08's A3, filled across/down: =IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$T:$T,,MATCH(WSN,'Quote Log'!$U$2:$IV$2,0))),"",INDEX('Quote Log'!B:B,MATCH(SMALL(OFFSET('Quote Log'!$T:$T,,MATCH(WSN,'Quote Log'!$U$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$T:$T,,MATCH(WSN,'Quote Log'!$U$2:$IV$2,0)),0))) (b) And to get the top range lines in Jan 08 to then appear in auto-chrono order by Actual Delivery dates within each weekly ranges part Make these similar tweaks: In Jan08's U3, filled across/down: =IF($F3<"Yes","",IF(AND($M3=U$2,$M3<=U$1),$M3+RO WS($1:1)/10^10,"")) Then in Jan08's A91, filled across/down: =IF(ROWS($1:1)COUNT(OFFSET($T$3:$T$66,,MATCH($D$8 9,$U$2:$Y$2,0))),"",INDEX(A$3:A$66,MATCH(SMALL(OFF SET($T$3:$T$66,,MATCH($D$89,$U$2:$Y$2,0)),ROWS($1: 1)),OFFSET($T$3:$T$66,,MATCH($D$89,$U$2:$Y$2,0)),0 ))) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600, Files:362, Subscribers:60 xdemechanik --- |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Errata:
In Quote Log's U4, filled across/down: =IF($G4<"yes","",IF(TEXT($K4,"mmmyy")=U$2,K4+ROW( )/10^10,"")) Should be: =IF($G4<"yes","",IF(TEXT($K4,"mmmyy")=U$2,$K4+ROW ()/10^10,"")) (.. $K4, not K4. The all important $ sign was missed) Here's the revised sample: http://freefilehosting.net/download/40had -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600, Files:362, Subscribers:60 xdemechanik --- |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a row using a formula
Thanks Max have made the change
"Max" wrote: Errata: In Quote Log's U4, filled across/down: =IF($G4<"yes","",IF(TEXT($K4,"mmmyy")=U$2,K4+ROW( )/10^10,"")) Should be: =IF($G4<"yes","",IF(TEXT($K4,"mmmyy")=U$2,$K4+ROW ()/10^10,"")) (.. $K4, not K4. The all important $ sign was missed) Here's the revised sample: http://freefilehosting.net/download/40had -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600, Files:362, Subscribers:60 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPYING SUM/IF FORMULA | Excel Discussion (Misc queries) | |||
Copying formula | Excel Worksheet Functions | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
Copying Formula | Excel Discussion (Misc queries) | |||
Copying formula | Excel Discussion (Misc queries) |