Hello fellow community members,
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.
I’m sure that someone will be around to help you with a specific way of doing this.
For now, though, I suggest that you read the following Help topics:
If you've been following along with this guide, your table is starting to look pretty robust, with different field types and a variety of different views. (If you haven't been following along with...
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?
I think the most reasonable comparison is this:
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:
Hi @Chris_Hamoen - I don’t know if this helps with your more complicated scenario, but I would do the sales forecast like this:
Instead of having 5 columns, one for each currency, have one column for forecast value and a second for the currency:
The currency field is a link to a currency table where you can specify the currency conversion rate:
Back in the forecast table, you can look up the conversion rate and then multiply out to get the (in this case) USD value: