Came up with this idea a while ago and I remembered it had a few more edge cases that I can remember for now.
Say you have a table with a Name column. The name of each row is constructed from other column(s) in the table, in a consistent way. For example, say it is a table for recording meeting events and there are two types of meetings: “Stand-up” and “Review”, that are numbered (e.g. “Stand-up 3”). These values are held in a Type and Number column. The name must adhere to the schema “[Type] [Number]”.
| Name | Type | Number |
---------------------------------------
...
| Stand-up 3 | Stand-up | 3 |
| Review 8 | Review | 3 |
...
One option to input new rows is to simply have the Name field a text input, and try to parse what the user types to produce the values in the Type and Number columns.
If the user makes a typo or forgets to type one component, it may be impossible for the formulas in the Type and Number columns to extract the needed data from the name. It also may be tedious to write the Name each time when the defined options can simply be selected instead.
So instead, one might choose to instead lock the Name field as a formula that composes the meeting name from the values selected by the user for the Type and Number column inputs. Although this solves the issues above, it means that you must display both columns whenever the table is viewed somewhere where the name might be changed, in addition to the final produced Name for a more legible version. Formulas that manipulate rows in this table must also know that the Name column is simply a view of the data in the underlying columns.
The change I am proposing is to make all the Name, Type, and Number fields “inputtable formulas”. That is, formula cells that depend on data in another column that is itself a formula depending on its own value. This of course creates a dependency cycle, so the way to mitigate this would to make the relationship stateful. Each end of the relationship describes how to map the data it would receive if the data were inputted on the other end. Then when data is inputted on one end, the cell at that end takes on that value, and the cells on the other end update according to the defined mapping (they are kept in sync). Conversely, it a change is inputted on the other end, it will update the synced value in the other cell that was previously modified according to the mapping defined in the other direction.
So long as the produced value conform or can be mapped to the other end, the cycle stays valid and can be modified from either end.
I see this creating problems with formulas or automation that update many fields in an entire row, but there could easily be a policy to decide the behaviour in a deterministic way when values that don’t correspond to each other are inputted at the same time for both ends.
I don’t think this creates any new behaviour when data inputted on one end cannot conform to the defined mapping, the other end would just become Blank or some other value produced by the formula, and going the other way afterwards would work if there is only one cell at each end, but would produce a deterministic (but garbage) value if there is more than one cell at the “source” end (as one of the “source” cells would be blank or garbage).
Lastly, this is a fairly complex type of workflow, so it could be worked into a simpler or more prevalent use case of “inputtable” formulas that simply allow an inputted text value to override the formula value. This in itself I believe has some value, perhaps as an augmented “Default value” that allows a cell to take on a dynamic default value from say other fields of its row, but allow the user to override the default at a later time.