I have 4 worksheets named calculation, 2004, 2005 & 2006.

On the calculation sheet I have a user that enters these details in
these cells:

Range A2 = commencement year
Range B2 = amount on records

The commencement year enters determines if sheet 2004, 2005 or 2006
should be used to look up cell A1 for the correct rate to use.

I want Range C2 to do this calculation:

('amount on records' X 'rate for the commencement year')/1000

so for example, if the commencement year was 2006 the formula would
be;

(B2*'2006 Rates'!A1)/1000

my problem is, I dont know how to factor in the comencement year as it
varies depending on what year is input. any help would be
appreciated.

thanks


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=517705

=(B2*INDIRECT("'"&A2&" Rates'!A1"))/1000


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rocket0612" wrote
in message ...

I have 4 worksheets named calculation, 2004, 2005 & 2006.

On the calculation sheet I have a user that enters these details in
these cells:

Range A2 = commencement year
Range B2 = amount on records

The commencement year enters determines if sheet 2004, 2005 or 2006
should be used to look up cell A1 for the correct rate to use.

I want Range C2 to do this calculation:

('amount on records' X 'rate for the commencement year')/1000

so for example, if the commencement year was 2006 the formula would
be;

(B2*'2006 Rates'!A1)/1000

my problem is, I dont know how to factor in the comencement year as it
varies depending on what year is input. any help would be
appreciated.

thanks


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile:
http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=517705

thanks bob, the formula works and returns the value, however, I am
getting a type mismatch error, why is this?

thanks


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=517705