combine 100 different formula

hope you can help me on my problem. i have attached my example
below

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


* Is that workbook supposed to make sense to anyone else? *

it is possible but you have to write your formulas in such a way that they can be interpreted in the current row context-which is why I used INDEX and ROW() in the attached version of your file.

Pl See the attached file.

Originally Posted by JosephP

it is possible but you have to write your formulas in such a way that they can be interpreted in the current row context-which is why I used INDEX and ROW() in the attached version of your file.

can you explain how this works. i was trying to do it with indirect however it failed. im just interested in the workings behind it

the formulas are entered as strings that would work if you copied them into the correct cell and added = before them
in the defined name CalculateItem the correct string is looked up from the table using VLOOKUP and passed to an EVALUATE function which then calculates it as a formula.

evaluate cannot be used directly in a worksheet because it is an xlm macro function but you can work around that problem by using it in a defined name. unfortunately it does not work with indirect or with r1c1 references unless you have excel set to use r1c1