Excel Help - Iterative Formula

[Makhaira]

Evening all,

I'm trying to sort out a formula that is currently a) massive and b) used in a lot of cells in a worksheet. Looking at the way it's structured, in most programming languages I'd be happy about how to shrink it right down in size using a loop, but in Excel I have no idea how to do this.

A snippet of the formula is below:

VLOOKUP($D$14&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&14)
+VLOOKUP($D$15&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&15)
+VLOOKUP($D$16&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&16)
+VLOOKUP($D$17&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&17)
+VLOOKUP($D$18&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&18)

And so on and so on, for about 40 lines. Repeated in several hundred cells. Getting the picture now?

All I really want to do is increment the parts in bold below, start at 2 and running to a value "n". Any kind soul know how to do this in Excel please?

VLOOKUP($D$14&$C41,'Embodiment Profiles'!$B$4:$AL$76,E$40,FALSE)*INDIRECT($B41&14)


Thanks!

 

[namnoc]

Have you looked at righting the code as a macro VBA instead of having it in the cells? That way you can do all the clever stuff you would normally. The cells can be addressed just like a two dimensional array.