Reducing Formular length

[Guest]

Hi I have a formular that is very long in a workbook I am currently using.
How can I reduce the length of teh formular? The sample formular is presented
below:

='Deployment Cost WorkSheet'!$D50*'Current Dep Plan &
Expec.'!E$14+'Deployment Cost WorkSheet'!$E50*'Current Dep Plan &
Expec.'!E$17+'Deployment Cost WorkSheet'!$F50*'Current Dep Plan &
Expec.'!E$20+'Deployment Cost WorkSheet'!$G50*'Current Dep Plan &
Expec.'!E$23+'Deployment Cost WorkSheet'!$H50*'Current Dep Plan &
Expec.'!E$26+'Deployment Cost WorkSheet'!$I50*'Current Dep Plan &
Expec.'!E$29+'Deployment Cost WorkSheet'!$J50*'Current Dep Plan &
Expec.'!E$32+'Deployment Cost WorkSheet'!$K50*'Current Dep Plan &
Expec.'!E$35+'Deployment Cost WorkSheet'!$L50*'Current Dep Plan &
Expec.'!E$38+'Deployment Cost WorkSheet'!$M50*'Current Dep Plan &
Expec.'!E$41+'Deployment Cost WorkSheet'!$N50*'Current Dep Plan & Expec.'!E$45

Any suggestions will be helpful.

 

[Bernie Deitrick]

Nello,

There is no easy way to do that, since you have unequal offsets. But, you
could simplify your formula by using helper cells:

In cell A1, enter the formula:

=OFFSET('Current Dep Plan & Expec.'!$E$14,(COLUMN()-1)*3,0)

In cell A2, enter the formula:
='Deployment Cost WorkSheet'!D50

Copy those two cells, and paste into B1:K2.

Then use the formula
=SUMPRODUCT(A1:K1,A2:K2)

to give the same result as your long formula.

Using this technique makes it easier to extend the formula when needed.

HTH,
Bernie
MS Excel MVP

 

[Biff]

Love those long sheet names! <hint>

Biff