Posted to microsoft.public.excel.worksheet.functions
|
|
Discerning data by the date
Thanks for feedback
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Andrew M." wrote in message
...
Thanks alot, Bernard. Your help was easy to understand and did what i
needed
it to.
"Bernard Liengme" wrote:
In Sheet1 beginning in A1 I have you data
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00
But I add in D1 the formula =YEAR(A1)&B1 and copy this down the column
On Sheet2 beginning in A1 I have
Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85
The formula in B2 that returns the value of 6.00 is
=INDEX(Sheet1!$C$1:$C$3,MATCH(B$1&$A2,Sheet1!$D$1: $D$3,0))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Andrew M." wrote in message
...
I wish to know how to have Excel discern different rows by the date.
Sheet1 (Columns)
-Date
-Part #
-Cost
Sheet2(Columns
-Part#
-2006
-2007
-2008
EX. Sheet1
Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00
Based on the above information, I want Sheet2 to look like
Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85
The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out
which
date goes under which column, and post the right cost in the right
place.
Any ideas on how to do this?
Andrew Murray
|