Calculating a value in table one from values in table 2

[Chris_Hamoen]

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!

[Claudio]

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:

Airtable - Support

Getting started: making new tables and linking records

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...

Airtable - Support

Linked record fields

A linked record field allows you to represent the relationships between related records by creating links between them. This is particularly helpful when you have multiple tables of related items o...

And Lookup fields.

[Bill_French]

Chris_Hamoen:

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.

[Chris_Hamoen]

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.

[Bill_French]

Chris_Hamoen:

Which means lookups and linked records isn’t applicable really.

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?

[Chris_Hamoen]

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).

[nea_lpatil]

@Chris_Hamoen

I created the base trying to show what you would have in excel sheet1 and sheet2.
Does it show what you are describing ?

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

Thanks,
Neal

[Bill_French]

Chris_Hamoen:

my actual use case is at least 2 orders of magnitude more complicated

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.

[Chris_Hamoen]

nea_lpatil:

Does it show what you are describing ?

Yes, and this is the airtable solution. However, imagine I have 50 currencies, but then also localized transfer pricing. :slightly_smiling_face:

Bill.French:

I do not have a solution to toss out just yet, but I certainly admire the problem. Still noodling.

Thanks. I have 2 options if we still use airtable:

1. go ahead and be ok with hundreds of columns
2. 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.

[JonathanBowen]

Chris_Hamoen:

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.

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