Is it possible to convert a string into a formula?

1

I’m looking for something like INDIRECT in sheets/excel.

In the same column I’d like to reference different arguments in different rows (like multiple named formulas that I used somewhere else in the doc).

So the question is can I type [table 1].[column 2] in a text type cell (without using the “=” sign in the beginning) and use it as a reference in a more complex formula in another column?

Thanks!

2

could you share your example?

3

My actual use case is that I’d like to create a smart contract assembly tool. The goal is to generate contracts by combining a long set of available section/term templates and link/merge the different variables to our data sets.

Here is my experience so far:

1st attempt:
Simple text + inline references:

1st%20attempt
1st attempt.png1026×98 11.1 KB

This is cool but this way I can only use one set of sections/terms because as far as I know there is no option to hide/display a specific paragraph. Or is there such an option I just missed it?

2nd attempt:
To make it more flexible and database-like I started to use the =Format() function. Here I have a merge template and a list of variables:

2nd%20attempt
2nd attempt.png1083×171 33.6 KB

This way I could create a table supposed to contain all the necessary inputs to generate all the available sections/terms:

3rd%20attempt
3rd attempt.png1760×391 57.4 KB

This way based on the “Term type” column information I could generate filter the appropriate terms for the selected contract.

BUT the problem is that in Var1, Var2, Var3 etc… columns I’d need different references for each row. If I use the “=” sign when describing a reference for Var1 it gives the same reference for all rows.

3rd attempt / idea:
It’s still not the best option but if I could use the “Var” columns to define the references with simple text and somehow use the content of these cells as part of a complex formula (such as Format) that would be a great workaround for now:

4th%20attempt
4th attempt.png1744×512 33.1 KB

I know this is might not be the ideal use case of coda tables at this point. But if you have any solution for this or a totally different idea to achieve the goal (contract assembly tool based on a long list of template terms/sections using variables) I’d be very thankful because I 'm out of alternative ideas at this point :slight_smile:

Thanks a lot!

2 Likes
4

Just wanted to note that this is a brilliant use case of Coda and I think would be an awesome feature to have available. I often find that even in simpler situations I would like the idea to set an environmental variable and use it repeatedly.

For example:

I have a lot of customers in a Customer Table.

For big customers, I want to display them in their own section.

I want to show a lot of information:
A View of the Customer Table with only the applicable customer.
Some bits of information that’s dependent on that table.
A Selector that allows the user to select attributes of the customer to show.

I can set an environmental variable with

image
image.png730×342 7.91 KB

Then I can copy and paste the contents to a new Section and all I have to do it change the customer_name for that sheet and it all updates!

All if it, that is, except for the Selector table because that relies on a reference to the name of the View of Customer Table, whatever I may have changed that to locally. This, of course, makes it break.

If there was a way to interpret a string as Coda syntax, then this would be much easier.

I could do something like:

table_to_reference = "[Table Name]"
table_to_reference.toCoda().Filter(...).[Column Name]

4 Likes
5

@Zsolt_Bako is it possible to share a working version to see what you’re trying to do?

I have done something similar. What about creating a table with all of the possible sections of the contract. Then create new tables for each new contract instance which matches the structure of the prior one; you can have a column like [Active?][Order][FieldValue]. Order can find its own rank amongst the active rows using a formula like this Rank(thisRow.[Row ID]=thisRow.[Active RowIDs]).

Then use the canvas with hardcoded formulas to generate the document by referencing this second table, pulling in =NewContract.filter(Order=1).[FieldValue] then have a bunch of hardcoded formulas with Order=Number of possible fields in the document. The inactive ones will render blank and you should just have the pertinent info!

6

@Zsolt_Bako I’d love to help you solve this — seems like a decent challenge.

There’s no indirect or any way to evaluate strings as formulas — at least not that I’m aware of.

Here’s the options that come to my mind:

Option 1

  1. Use your “1st attempt” approach with simple text + inline references. Make each paragraph a named formula
  2. Add named checkboxes before each paragraph
  3. On a separate section, write a formula that will concatenate all paragraphs, but omit the text if the switch is disabled:

Pro: easy
Con: need to update the concatenation formula every time you change the number of sections.

Option 2

Follow the 2nd attempt with a table and VarX columns, and make a button that will populate those var columns with the data from other tables. You can do this with RunActions(ModifyRows(...), ModifyRows(...)... chain of actions to edit every row individually, putting different data into Var cells.

Pro: you have it neatly organized in a table, so filtering and concatenating enabled clauses is super easy.
Con: writing that button formula is a PITA. Also you’ll have to remember to press the button every time data is changed. Or you can write Automation rules to trigger that button every time a change in original data is detected.

P.S. Only now I’ve seen that this question is 1y old. Oh well, hope this still helps somebody :sweat_smile: