Use the same formula multiple times for multiple cells containing dropdown lists

Question

My file looks something like this image
An IF statement would be too long considering I have 20 different long formulas.

Update: I later tried to use an IF statement but failed because it depended on substitute function to replace cell references in the equation but substitute returned a string which coudn't be used as a function by IF.

User should be able to increase available rows by simply copy and insert the previous row.

I can use index match to copy a cell's value which contain the right formula according to the dropdown list but when the user select the same item again I can't update the formula with the new values without affecting the previous cell which used the same formula.

I couldn't find a way to copy the same formula several times and replace cell references in it (without human interaction like search and replace) i.e. by using Substitute function which couldn't replace cell references as it looks through the cell's value not it's formula (the cell which contain the main formula).

Here is one of the 20 formulas I have
=(Tables!O167*144/(Tables!O158*Tables!O159)/4005)^2*INDEX(Tables!A159:L200;MATCH(INDEX(Tables!A159:A200;MATCH(TRUE;INDEX(Tables!A159:A200>=Tables!O158*Tables!O159/(Tables!O160*Tables!O161);0);));Tables!A159:A200;0)+MATCH(INDEX(Tables!B159:B200;MATCH(INDEX(Tables!A159:A200;MATCH(TRUE;INDEX(Tables!A159:A200>=Tables!O158*Tables!O159/(Tables!O160*Tables!O161);0);));Tables!A159:A200;0)+MATCH(INDEX(Tables!B159:B200;MATCH(TRUE;INDEX(Tables!B159:B200>=Tables!O164/(2*Tables!O158*Tables!O159/(Tables!O158+Tables!O159));0);));Tables!B159:B200;0)-1;);Tables!B159:B200;0)-1;MATCH((INDEX(Tables!C158:L158;MATCH(TRUE;INDEX(Tables!C158:L158>=Tables!O163;0);)));Tables!A158:L158;0))

I tried to use FORMULATEXT to convert the formula into a string then use substitute to replace the cell references then use the depreciated Evaluation function but hit the 255 char limit.

I searched a lot on google but to no avail, I don't mind a VBA code but a macro free method would be better, Thanks.

TL;DR: Is there a way to copy a formula stored in a cell and replace some of the cell references then enter it in another cell multiple times with different cell references each time ??

I gather than H2:J2 is actually B2:D2 but why isn't the square formula =H2^2? — user4039065
It's just an example it's not really a square formula and most of the cell references are from another sheet with 3 to 5 cell references that need to be changed with the cells that the user will fill — mostafa
fwiw, isn't x typically the base, not the height? Might be confusing in its current form. — user4039065
Make the jump to VBA or Python; you'll be happier for it. Programming in Excel formulas is fun and accessible, but the limitations quickly pile up into something horrible. — ti7
jumping into VBA is something that I will have to do eventually but I can't right now and for sure I can't start learning VBA to accomplish this goal and expect quick results :) — mostafa

Unknown Unknown · Accepted Answer · 2018-08-01T21:44:10

Try this in E2 and fill down.

=CHOOSE(MATCH(LOWER(LEFT(A2)), {"r","s","t"}, 0), B2*C2, B2^2, B2*C2/2)

Use the same formula multiple times for multiple cells containing dropdown lists

1 Answers