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

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

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

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


--
Biff
Microsoft Excel MVP


"GB" wrote in message
...
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

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

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.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"GB" wrote in message
...
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

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

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

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"GB" wrote in message
...
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

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

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

--
Biff
Microsoft Excel MVP


"GB" wrote in message
...
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.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"GB" wrote in message
...
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