I need to pull rows from a List where a date value is less than the first day
of the month and have the rows that do not meet the specific date copied to
another sheet.

Example of my data: (Criteria = Column H, where date is <12/1/2006)

Sheet 1
A B C D E F G
H
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006
Row 2 MF OPCO 008702 Text 2 X 42,000 3.10% 12/11/2006

Sheet 2
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006

Row 1 would be copied from Sheet 1 and duplicated on Sheet 2 because the
date in Column H is less than 12/1/2006 (I would make the date criteria a
Named Range).

If possible, ideally just Columns A, B, C, D, E and H would be copied
instead of the entire row of information.

If it was possible to Advance Filter to another sheet - this wouldn't be a
challenge!

I appreciate your assistance.

Thank you,

--
aaghd

Look in Help menu for INDEX and MATCH Functions

"aaghd" wrote:

I need to pull rows from a List where a date value is less than the first day
of the month and have the rows that do not meet the specific date copied to
another sheet.

Example of my data: (Criteria = Column H, where date is <12/1/2006)

Sheet 1
A B C D E F G
H
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006
Row 2 MF OPCO 008702 Text 2 X 42,000 3.10% 12/11/2006

Sheet 2
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006

Row 1 would be copied from Sheet 1 and duplicated on Sheet 2 because the
date in Column H is less than 12/1/2006 (I would make the date criteria a
Named Range).

If possible, ideally just Columns A, B, C, D, E and H would be copied
instead of the entire row of information.

If it was possible to Advance Filter to another sheet - this wouldn't be a
challenge!

I appreciate your assistance.

Thank you,

--
aaghd

Thanks, I was looking through some other threads (in particular - a thread
from Mick - subject: Listing data based on a referneced criteria - dated
12/9/2006 3:09 PM PST) and figured that I can probably use the Index and the
Match functions but for the life of me, even after reading the help topics
and reviewing their example, I am not sure of how to write a formula for my
particular situation.

Was hoping that one of you Excel/Math wizards could throw one together for me.

--
aaghd


"Teethless mama" wrote:

Look in Help menu for INDEX and MATCH Functions

"aaghd" wrote:

I need to pull rows from a List where a date value is less than the first day
of the month and have the rows that do not meet the specific date copied to
another sheet.

Example of my data: (Criteria = Column H, where date is <12/1/2006)

Sheet 1
A B C D E F G
H
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006
Row 2 MF OPCO 008702 Text 2 X 42,000 3.10% 12/11/2006

Sheet 2
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006

Row 1 would be copied from Sheet 1 and duplicated on Sheet 2 because the
date in Column H is less than 12/1/2006 (I would make the date criteria a
Named Range).

If possible, ideally just Columns A, B, C, D, E and H would be copied
instead of the entire row of information.

If it was possible to Advance Filter to another sheet - this wouldn't be a
challenge!

I appreciate your assistance.

Thank you,

--
aaghd

Try this page on the Debra Dalgleish site

http://www.contextures.com/xlautofilter03.html

Tom Ogilvily has writeen a macro to copy filtered data onto anoth sheet.

Regards
Peter
"aaghd" wrote:

Thanks, I was looking through some other threads (in particular - a thread
from Mick - subject: Listing data based on a referneced criteria - dated
12/9/2006 3:09 PM PST) and figured that I can probably use the Index and the
Match functions but for the life of me, even after reading the help topics
and reviewing their example, I am not sure of how to write a formula for my
particular situation.

Was hoping that one of you Excel/Math wizards could throw one together for me.

--
aaghd


"Teethless mama" wrote:

Look in Help menu for INDEX and MATCH Functions

"aaghd" wrote:

I need to pull rows from a List where a date value is less than the first day
of the month and have the rows that do not meet the specific date copied to
another sheet.

Example of my data: (Criteria = Column H, where date is <12/1/2006)

Sheet 1
A B C D E F G
H
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006
Row 2 MF OPCO 008702 Text 2 X 42,000 3.10% 12/11/2006

Sheet 2
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006

Row 1 would be copied from Sheet 1 and duplicated on Sheet 2 because the
date in Column H is less than 12/1/2006 (I would make the date criteria a
Named Range).

If possible, ideally just Columns A, B, C, D, E and H would be copied
instead of the entire row of information.

If it was possible to Advance Filter to another sheet - this wouldn't be a
challenge!

I appreciate your assistance.

Thank you,

--
aaghd

Thanks. I created the macro and ran it and it runs perfectly. In an attempt
at making this workbook more automated, I was hoping to get the list of
non-submittals generated authomatically when the date entered is not a
current date. This solution although it generates the list for me it takes
manually filtering and then manually running the macro. Do you have any
other suggestions?

I am working on rewriting a formula that I found on the following thread:
http://www.excelforum.com/showthread...hreadid=551680

I have accomplished the two formulas in the first box. I have it
identifying which lines are either Current or Non-Current submittals and I
created the Named Range. It is the IF statement in the second box of
information that is giving me some trouble reworking.

The information you sent me is awsome, none the less.

Thank you.
--
aaghd


"Billy Liddel" wrote:

Try this page on the Debra Dalgleish site

http://www.contextures.com/xlautofilter03.html

Tom Ogilvily has writeen a macro to copy filtered data onto anoth sheet.

Regards
Peter
"aaghd" wrote:

Thanks, I was looking through some other threads (in particular - a thread
from Mick - subject: Listing data based on a referneced criteria - dated
12/9/2006 3:09 PM PST) and figured that I can probably use the Index and the
Match functions but for the life of me, even after reading the help topics
and reviewing their example, I am not sure of how to write a formula for my
particular situation.

Was hoping that one of you Excel/Math wizards could throw one together for me.

--
aaghd


"Teethless mama" wrote:

Look in Help menu for INDEX and MATCH Functions

"aaghd" wrote:

I need to pull rows from a List where a date value is less than the first day
of the month and have the rows that do not meet the specific date copied to
another sheet.

Example of my data: (Criteria = Column H, where date is <12/1/2006)

Sheet 1
A B C D E F G
H
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006
Row 2 MF OPCO 008702 Text 2 X 42,000 3.10% 12/11/2006

Sheet 2
Row 1 RH MDA 057202 Text 1 X 10,000 2.00% 10/10/2006

Row 1 would be copied from Sheet 1 and duplicated on Sheet 2 because the
date in Column H is less than 12/1/2006 (I would make the date criteria a
Named Range).

If possible, ideally just Columns A, B, C, D, E and H would be copied
instead of the entire row of information.

If it was possible to Advance Filter to another sheet - this wouldn't be a
challenge!

I appreciate your assistance.

Thank you,

--
aaghd

Thanks. I created the macro and ran it and it runs perfectly. In an
attempt
at making this workbook more automated, I was hoping to get the list of
non-submittals generated authomatically when the date entered is not a
current date. This solution although it generates the list for me it takes
manually filtering and then manually running the macro. Do you have any
other suggestions?

I am working on rewriting a formula that I found on the following thread:
http://www.excelforum.com/showthread...hreadid=551680

I have accomplished the two formulas in the first box. I have it
identifying which lines are either Current or Non-Current submittals and I
created the Named Range. It is the IF statement in the second box of
information that is giving me some trouble reworking.

I would record a macro while you set the filters then before the End Sub
statement you can call Tom's macro so the end of the macro would be

MySub()
Recorded Code
.....
CopyFilter
End Sub

This combines both operations. You could create a worksheet code that calls
The MySub procedure By current date I assume that you mean Today()

Say the Dates are in column A; the code would be something like

Set target = Range("A2:A400")
if isdate(target) and target < date then
MsgBox "I'll run the code"
End If
Rest of the code here

With regard to your other formula Is this what you are after

Function CountDecs(LastName As String, FirstName As String, Decs As String)
As Long
Dim count As Integer, c, LName As Range
Dim fname, rej
ns = Worksheets.count
count = 0
Set LName = Range("a9:a15")
For i = 1 To ns
With Worksheets(i)
For Each c In LName
fname = c.Offset(0, 1)
rej = c.Offset(0, 3)
If c = LastName And _
fname = FirstName And _
rej = Decs Then
count = count + 1
End If
Next c
End With
Next i
CountDecs = count
End Function

This will give you the count persons in every sheet in the workbook (You
can add new sheets later)

It assumes that The Columns are A, B and D all you need to change is the set
LName range to that of the first sheet and providing that the data is set out
the same in other sheets it will work.

Regards
Peter