Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet.

[bertbarndoor]

Hi, I was wondering if someone could help me...

I have rows in one sheet (call that the mastersheet) where one of the
columns is "date file received". I want all rows where that date is
less than 7 days copied over to another worksheet (call that the
reportsheet). That's it.

To complicate matters, on the reportsheet, below the result from the
above, I want another set of rows from the mastersheet pulled over
based on the column "Date file Actioned" where the date is within the
last 7 days.

Anyone??? Thanks,

Rob

I dont know VB at all. Any way to do this with just formulas?? TKS.

 

[sharmashanu]

hey
use this formula

=IF((Sheet1!A1-TODAY())<=7, Sheet1!A1, "")

drag this down. but his will bring empty cells. but will copy all the
cells with date less than 7 days. Tell me if it works.

 

[bertbarndoor]

No, it didn't really work. I want the entire row (many other columns
as well) to be brought over too. Let me illustrate

Mastersheet (dataset)

A B
C
Date file received Fruit1
Fruit2
1 Oct 07 Apples Oranges
2 2 Oct 07 Grapes Oranges
3 1 Jan 07 Apples Bananas
4 5 Feb 07 Grapes Pears
5 3 Oct 07 Pears Grapes


Report Sheet (desired result)
1 Oct 07 Apples Oranges
2 2 Oct 07 Grapes Oranges
3 3 Oct 07 Pears Grapes


How do I get the rows to come over within the last 7 days??? I want
the whole row.

Thanks

-Rob

 

[sharmashanu]

ok..to get the first seven days..use this formula in column 1

=IF(DAY(Sheet1!A2)<7, Sheet1!A2, "")

if you want the past 7 days then use the one i gave before

=IF((Sheet1!A1-TODAY())<=7, Sheet1!A1, "")

To get the second row use
=(VLOOKUP(A1,Sheet1!A2:E6,2,FALSE)) this will give second column
correspoding to that date

to get the third use

=(VLOOKUP(A1,Sheet1!A2:E6,3,FALSE)) and so on.

hope this helps

 

[bertbarndoor]

ok..to get the first seven days..use this formula in column 1

=IF(DAY(Sheet1!A2)<7, Sheet1!A2, "")

if you want the past 7 days then use the one i gave before

=IF((Sheet1!A1-TODAY())<=7, Sheet1!A1, "")

To get the second row use
=(VLOOKUP(A1,Sheet1!A2:E6,2,FALSE)) this will give second column
correspoding to that date

to get the third use

=(VLOOKUP(A1,Sheet1!A2:E6,3,FALSE)) and so on.

hope this helps

Won't a vlookup only work if the column is sorted in order? It isn't
and can't. I'm still stuck. Thanks so far though... -Rob