Copying a row using a formula

K

Kingdazza

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
 
M

Max

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,INDEX(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)),OFFSET(x!$F:$F,,MATCH(TRUE,INDEX(TEXT(x!$G$1:$K$1,"mmmyy")=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
 
M

Max

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)),OFFSET(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
---
 
K

Kingdazza

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.
 
M

Max

.. 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
 
K

Kingdazza

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
 
M

Max

.. 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?
Click to expand...

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
---
 
M

Max

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
---
 
K

Kingdazza

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 said:
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
---
Click to expand...
 
K

Kingdazza

Hi Max,

We have the worksheet up and running but I need help on one last formula. I
have attached the link for the worksheet.

http://freefilehosting.net/download/40emc

If you could have a look I would appreciate it, if you have any questions I
can be contacted directly at (e-mail address removed)

Thanks again.

Darren

Max said:
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
---
Click to expand...
 
M

Max

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 here:
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$89,$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$113,$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
---
 
K

Kingdazza

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.
 
M

Max

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.
Click to expand...

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+ROWS($1:1)/10^10,""))

Then in Jan08's A91, filled across/down:
=IF(ROWS($1:1)>COUNT(OFFSET($T$3:$T$66,,MATCH($D$89,$U$2:$Y$2,0))),"",INDEX(A$3:A$66,MATCH(SMALL(OFFSET($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
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Populate Delivery Note 0
generating consecutive sheets with macros 2
Help With Conditional Formatting 12
Creating a formula 1
Outlook Calendar Form Reload? 5
HELP!!! 1
Excel Drag autofill only part of formula 3
Automate date & time entry 1

Top