Mike,
Works like a charm.

Thank's so much!

Pierre

Rich, thanks for your input as well


On Apr 25, 2:11*pm, "Rich/rerat" wrote:
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

"Pierre" wrote in message

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

Pierre

On Apr 25, 12:24 pm, Mike H wrote:



try this

=SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4))

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

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

Mike

"Pierre" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -