Formula based on criteria?

  • Thread starter Thread starter MrAlMackay
  • Start date Start date
M

MrAlMackay

Is it possible to do the following with a formula?

Need to be able to only show rows (hoping to do this using 'AutoFilter'
option?) where the date is column E is =< 7 days ago.

Hope this makes sense. Basically this is for a weekly report and they have
this data taken out to another sheet as part of the process - unfortunately @
the moment this is done manually which is a tedious task and could be tidied
up?

Any help would be appreciated.

Cheers, Al Mackay ( (e-mail address removed) )
 
Not sure with Auto-Filter, but with Advanced-Filter, in the Criteria Range
(say E1:E2 for example) it should be set to:

E
1 MyDateColumnName
2 =">="&(TODAY() -7)

HTH
 
try this where checksA is a defined name range
or change to c In range("whatever")

Sub weeklyreport()
For Each c In [checksA]
If c >= Date - 7 Then
c.EntireRow.Copy _
Sheets("sheet1").Range("a" & Sheets("sheet1") _
..Cells(65536, 1).End(xlUp).Row + 1)
End If
Next
End Sub
 
Thanks Don

Had a look @ the previous postings but they were slightly different than what
I'd wanted anyway.

Going to go down the VBA route and see if anyone can help me from the
Programming newsgroup in doing this.

Appreciate your help on this.

Many Thanks, Al Mackay
( (e-mail address removed) )
 
Al

Here's one way:

Assuming data in A2:E???
Headings in A1:E1
Dates In column E

1. H1 empty
2. In H2 enter this formula =E2>=TODAY()-7
3. Place the cursor in a data column.
4. Choose Data > Filter > Advanced filter
5. For criteria range enter H1:H2

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Back
Top