I have a sheet (Sheet1) which has multiple columns - say ConfigValue1, ConfigValue2. Then I have a column called Result that calculates a result using those values and values (Logic1, Logic2) from Sheet2.
In Sheet1:Result, I want Sheet1:ConfigValue1 x Sheet2:Logic1 + Sheet1.ConfigValue2 x Sheet2:Logic2.
Note that the logic could be a bit more complicated.
I could put the logic right into Sheet1, but the issue is that we want to have a separate interface for entering logic.
Chris, yeah - I can see why you’d want to do this. I have run into this requirement a few times myself.
I think what you’re saying is you’d like to have a way to program cell outcomes based on formulas that target arbitrary tables and records. Perhaps like a master control table with declarative statements that are interpreted and applied to affected tables/rows/cells. Am I on the right compass heading?
This - I presume - would embrace a write-once/use everywhere logical approach that makes it easier to redefine the logic without revisiting every place where the outcomes occur.
Well, it certainly means that attempting to craft an approach based on lookups and linked records would be extremely convoluted and unlikely to work.
There’s no indication in your example formula how the values in each column relate to the rows in each table. For example - given this computation across any row in Sheet1, which record/Logic1 value should be used? Is it based on a linked key between Sheet1 and Sheet2?
Sheet 1 is a sales forecast. Forecast values are all in different columns in different currencies.
Sheet 2 are currency conversion values.
Goal is to create a column in Sheet1 that converts all the different column currencies into a single sales forecast value. Solution appears to be a “fake” linked table value to link them, then a column for every currency.
So if you have 5 currencies, you add 5 columns to Sheet 1 for the conversion lookup and a 6th column multiplying them and adding them up.
In excel you wouldn’t need to “duplicate” the values in sheet1.
The problem is my actual use case is at least 2 orders of magnitude more complicated (think: price configurator for a specing a building).
Right, I sensed that the example was the basis for a much more complex computational model. You are essentially describing the conceptual model hoping that an approach would be magically applicable to far more complex process formulas.
I use the term “process formula” here because you are describing computations that span potentially multiple passes through the records.
I do not have a solution to toss out just yet, but I certainly admire the problem. Still noodling.
Yes, and this is the airtable solution. However, imagine I have 50 currencies, but then also localized transfer pricing. :slightly_smiling_face:
Thanks. I have 2 options if we still use airtable:
go ahead and be ok with hundreds of columns
create a naming structure that our front end code understands, and use it in the columns. So the front end does the lookups, and reads the logic from the actual cell value. For instance, “euroCurrency x 0.75” in the logic sheet, and the column name in the main worksheet is euroCurrency. Not elegant, but a possibility.