MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditionally copying a value from one sheet to another...


Posted by Dustin on March 07, 2001 8:35 AM

How would I go about copying records from one sheet to another with certain criteria.

Here is an example:

----------A------------B-------------C-------
1-------PO #------Start_Date------End_Date-----
2----000012345-------2/12/01------2/15/01----
3----000098765-------2/14/01------2/23/01-----

How would I copy the values from the A column that fall within certain date ranges (values contained in the B and C columns) to another sheet?

Thanks
Dustin


Posted by Mark W. on March 07, 2001 3:14 PM

Dustin, all you really need to do is AutoFilter
this list. Let's say that you want to copy the
PO's with a start date <= to 2/13/01 and an end
date >= 2/13/01. Just assign a custom filter
to column B with a condition of "is less than
or equal to" 2/13/01. Assign an additional
custom filter to column C (you're AND-ing) with
a condition of "is greater than or equal to"
2/13/01.

The great think about AutoFiltered lists is that
the Copy/Delete commands only operate on the
visible rows (those with blue row numbers).
This allows you to copy column A and paste them
into another worksheet with the knowledge that
you're only pasting those PO's that match your
criteria.

Posted by Dustin on March 08, 2001 6:25 AM

Mark, Thanks!, a few more questions though...

Please forgive my ignorance on this, I am fairly new to Excel, but your answer prompted a few more questions:

1) I completely understand how to do what you said manually, I do not know how to set this up so it is automatic and incremental (meaning that I do not want to keep copying and pasting rows that have already been copied over).

2) How would I copy over only the PO# and not the rest of the row(s)?

3) How would I use the autofilter concept if I had four date ranges (I need to track quarterly activity: Jan-March, April-June, etc)? I have a different destination sheet for each of the quarters.

Thanks again for your help.
Dustin

Posted by Mark W. on March 08, 2001 6:52 AM

Re: Mark, Thanks!, a few more questions though...

Dustin, please provide more details of your
objective and requirements...

> How would I use the autofilter concept if I
> had four date ranges (I need to track quarterly
> activity: Jan-March, April-June, etc)? I have a
> different destination sheet for each of the
> quarters.

Your data has 2 dates. Which should be used as
the basis for the quarterly classification?

Posted by Dustin on March 08, 2001 8:05 AM

Re: Mark, Thanks!, a few more questions though...

Mark,
They should both be used, let me explain;
I need to include a PO in any and all quarters in which activity was performed. Basically, the start date and end date would show when a contractor started on an assignment and ended an assignment.
So the date range parameters for the first quarter would be a start date that was less than 4/01/01 and an end date that was greater than or equal to 1/01/01 (OR blank in case there was no end yet) to capture any records that fell into the range.
Hopefully this helps some.

Thanks again.
Dustin

Dustin, please provide more details of your

Posted by Dustin on March 08, 2001 8:08 AM

Re: Mark, Thanks!, a few more questions though...

Mark,
One other point, I added another post just in case I didn't hear back from you today. I am running into a deadline soon and I did not know if you might be available today.
Thanks,
Dustin
Mark, They should both be used, let me explain; I need to include a PO in any and all quarters in which activity was performed. Basically, the start date and end date would show when a contractor started on an assignment and ended an assignment. So the date range parameters for the first quarter would be a start date that was less than 4/01/01 and an end date that was greater than or equal to 1/01/01 (OR blank in case there was no end yet) to capture any records that fell into the range. Hopefully this helps some. Dustin : objective and requirements... : > had four date ranges (I need to track quarterly : > activity: Jan-March, April-June, etc)? I have a : > different destination sheet for each of the : > quarters. : the basis for the quarterly classification?