Obtain rate for work center, depending on year

[Pierre]

On a worksheet, we have 72 work centers, and charge rates for each
year and each center in a table; going out 12 years.

W/C 2008 2009 2010 etc
1 15.00 16.98 18.10
2 23.98 24.25 26.98
3 11.00 13.85 15.65
etc.


Would like to obtain the rate for each workcenter on another sheet for
the year thats entered into cell A1 in the worksheet.

A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be
really clumsy as a place to start, as there are 12 years to chose
from, making the formulas particularly messy.

Thoughts?

Pierre

 

[Mike H]

try this

=SUMPRODUCT((A2:A4=2)*(B11=2008),(B24))

this looks for w/c 2 in A2:A4 and 2008 in B11 and returns the numeric
value at the intersect.

It would be better to make the 2 & the 2008 cell references.

Mike

 

[Pierre]

Thank you Mike, I'll have a run at it.

Pierre

try this

=SUMPRODUCT((A2:A4=2)*(B11=2008),(B24))

this looks for w/c 2 in A2:A4 and 2008 in B11 and returns the numeric
value at the intersect.

It would be better to make the 2 & the 2008 cell references.

Mike








- Show quoted text -

 

[Pierre]

I should have mentioned, we enter actual dates: 04/25/2009, and not
just the 4 digit year. Still need to return the vaules of the w/c for
that year.

Thanks Mike.

Pierre

 

[Rich/rerat]

Pierre,
You could use the HLOOKUP function.

1. With your rate table already created for center number and years.
a. Highlight the table> Insert> Name> Define> Type WorkCenterRate (or
other desired name)> Add.
Giving you a table reference of: =Sheet1!$A$1:$M$73
2. Create a New Sheet(2).
A1=Leave Blank
A2 to M2= Use the same headers that you used on Sheet(1).
A3 to A74= Number them 1 through 72
B3=the following formula:

=if($A$1="","",HLOOKUP(YEAR($A$1),WorkCenterRate,($A3+1))
The Pull down the formula in the B column

The "($A3+1)" references the row number to look at on sheet(1).


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Thank you Mike, I'll have a run at it.

Pierre

 

[Pierre]

Mike,
Works like a charm.

Thank's so much!

Pierre

Rich, thanks for your input as well