Obtain rate for work center, depending on year
 

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

Obtain rate for work center, depending on year
 

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

Obtain rate for work center, depending on year
 

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 -

Obtain rate for work center, depending on year
 

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



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 -

Obtain rate for work center, depending on year
 

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 -

Obtain rate for work center, depending on year
 

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 -