Ask Your Question
0

Is it possible to convert a text string to a formula?

asked 2018-02-22 17:14:49 +0200

hhaddow gravatar image

updated 2018-02-22 20:32:20 +0200

Kruno gravatar image

Is it possible to convert text to a formula or to otherwise run a formula in a different cell?

I am working on a series of spreadsheets for an RPG, each fine contains a dozen or so character sheets with identical formulae if I want to change one formula I have to then go through every player character and non-player Character sheet and paste in the new formula, is it possible to emulate Excel's Evaluate function?

for example, Formulas.B2 contains the formula =MID(CELL("filename",A1),FIND("$",CELL("filename",A1))+1,255) would it be possible to have something along the lines of =Evaluate(Formula($Formulas.B1)) that way only writing the formula once without having to make it a named expression it would also mean that canges to any formual will impact all sheets rather than one sheet at a time?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-02-22 22:15:58 +0200

Lupp gravatar image

updated 2018-02-22 22:17:33 +0200

You are not the first one to post such a question somewhere, and as far as I know nobody posted a satisfying solution. LibO obviously isn't just missing an EVALUATE function, but also the API does not provide a service for the purpose. (There is a service for parsing / tokenising a formula, however, but I don't know how to create an instance of it or how to use it.)

Thus the only way I can think of is to use helper cells for the task and to write some user code to manage it.
Let me explain:
Suppose you have a sheet S1 and you created a (probably hidden) sheet S0.
In OtherSheet.D11 you have the TEXT =FIND(°.A11;°.B11) and you want to get evaluated In S1.X11 the formula =FIND(S1.A11;S1.B11) created from the above text substituting a placeholder with a sheetname.
Enter =GETRESULT(PASSCELLZYX(SHEET()+1;ROW();COLUMN());SUBSTITUTE($OtherSheet.D11;"°";"S1")) into X11 of S1.
Of course, nothing will happen except that an error is reported.
You need to define some user functions:
PASSCELLZYX() passes the needed info about the position of the helper cell as an Array(1 To 1, 1 To 3). How the formula string is passed is clear.
GETRESULT() finally has to access the helper cell, to set the passed string as its formula, to get the formula result, and to return it to the calling cell.
Yes. It's so extremely simple.
Good luck!

You may play with the primitive version contained in this attachment.
Make sure to understand that I don't encourage you to use crotches of the kind. I did a lot with Calc for a few decades now, and never had to finally resort to something like that. In fact I very rarely wrote user functions, and if I did they were mostly mathematical.

You should expect the described method to be rather inefficient.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-22 17:14:49 +0200

Seen: 1,366 times

Last updated: Feb 22 '18