Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GB GB is offline
external usenet poster
 
Posts: 230
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GB GB is offline
external usenet poster
 
Posts: 230
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GB GB is offline
external usenet poster
 
Posts: 230
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Previous Month End Tristan Excel Discussion (Misc queries) 7 September 9th 06 12:25 PM
Date of the First day of the previous month Hari Excel Discussion (Misc queries) 3 May 20th 06 06:49 PM
automatically update chart plotting current month and previous 6 Jane Charts and Charting in Excel 1 September 1st 05 10:19 PM
Calculate the first day of the month for the current month? April S. Excel Discussion (Misc queries) 5 July 27th 05 08:53 PM
get the latest day of the previous month Laurent M Excel Discussion (Misc queries) 2 January 26th 05 04:22 PM


All times are GMT +1. The time now is 03:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"