The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Aug 26, 2019 09:27 AM
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.
Thanks!
Aug 26, 2019 12:37 PM
Hi Chris,
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...
Aug 26, 2019 03:43 PM
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.
Aug 28, 2019 07:41 AM
Exactly Bill.
The parallel in Excel is a main worksheet and a separate worksheet with various “settings” that can be changed.
Main worksheet would reference exact cells such as SettingsSheet.$C$3.
Which means lookups and linked records isn’t applicable really.
Aug 28, 2019 10:10 AM
Well, it certainly means that attempting to craft an approach based on lookups and linked records would be extremely convoluted and unlikely to work.
Question:
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?
Aug 29, 2019 11:36 AM
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).
Aug 29, 2019 02:37 PM
I created the base trying to show what you would have in excel sheet1 and sheet2.
Does it show what you are describing ?
Thanks,
Neal
Aug 30, 2019 06:53 AM
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.
Aug 30, 2019 07:17 AM
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:
Aug 30, 2019 07:22 AM
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:
JB