A command to help build spreadsheets
This helps build formulas for spreadsheets, including for use with HtmlETable, using position relative flags, or X, Y Lookup values. Only cells that start with an = and contain an even number of # symbols will have this logic applied to them.
For example, when trying to build the following formula:
You could set the following value in the field:
Here are the rules applied. They are loosely based on "R1C1 Reference Style"
- Each value to be solved is wrapped in # # symbols
- R stands for row and C stands for column
- #R# equals the current row, #C# equals the current column
- #R[-2]# or #C# reference negative or positive offsets of the current row or column
- #R['Start']# references the first row found that contains the value of Start in the first column
- #C['First Name']# references the column with a field name of First Name
- #R['Start']# references the first row found that contains the value of Start in the first column and then adds two
- #C['First Name'][-1]# references the first column found with a field name of First Name and then subtracts one
- #R[<'Start']# references the first row found searching backwards from the current row, which contains the value of Start in the first column, and then adds two</li>
- #C[>'First Name'][-1]# references the first column found searching forwards from the current column with a field name of First Name and then subtracts one
The only parameter for this command is a bool value, when set to true (not blank) will return the input for each cell as is when an error is found with a reference or a lookup. When this parameter is not used, these will instead replace those cells with a blank value which keeps the display clean when working near bound edges.