Posted to microsoft.public.excel.worksheet.functions
|
|
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 -
|