Retrieve data for previous 3, 6, 12 month given current month

[Guest]

I have a spreadsheet that lists data from certain wells on the date they were
taken. The date and data are listed down two separate columns. In another
worksheet, I need to retrieve and compare the data from the current month to
data from that same well 3, 6, and 12 months ago, is there anyway to have
Excel do this?
eg (comma separates columns):
From:
Date, Measurement
2/1/2007, 3000

I need:
1 year ago, Measurement
2/1/2006, 3003

 

[T. Valko]

You could do something like this:

Sheet2:

A1 = date to lookup = 2/1/2007
A2 = date 3 months age: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1))
A3 = date 6 months ago: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))
A4 = date 1 year ago: =DATE(YEAR(A1),MONTH(A1)-12,DAY(A1))

Then do a lookup of the dates:

Sheet2 B1:

=VLOOKUP(A1,Sheet1!A:B,2,0)

Copied down to B4

 

[Guest]

Thanks, is there any way for this data to update automatically as new data is
entered into sheet1. basically, to take the last date entered in column A of
sheet1, and then take the value from 3, 6, and 12 months ago to put into the
column of sheet2? I want to avoid having to update the formulas everytime
new data is entered. Thanks.

 

[Guest]

Nevermind, I see where you're going. Thanks!

You could do something like this:

Sheet2:

A1 = date to lookup = 2/1/2007
A2 = date 3 months age: =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1))
A3 = date 6 months ago: =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))
A4 = date 1 year ago: =DATE(YEAR(A1),MONTH(A1)-12,DAY(A1))

Then do a lookup of the dates:

Sheet2 B1:

=VLOOKUP(A1,Sheet1!A:B,2,0)

Copied down to B4

 

[T. Valko]

In Sheet2 cell A1 you can use this formula to get the last date entered on
Sheet1 from column A:

=LOOKUP(1E10,Sheet1!A:A)

Format as DATE