CODEWITHSUNDEEP
vbaexcelexcel-formula
Mostafa
Mostafa

Reputation: 148

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

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 ??

Upvotes: 1

Views: 100

Answers (1)

user4039065
user4039065

Reputation:

Try this in E2 and fill down.

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

enter image description here

Upvotes: 1

Related Questions