In short, you can’t.

This is because Airtable isn’t really a spreadsheet, but rather it’s a database. This means that it’s only possible to refer to other ‘fields’ (aka columns) and not other records (aka rows).

All that said, a lot of people have found workarounds. If you could explain what you are trying to do we may be able to help still.

Hi @bdelanghe! I’m trying to do the same thing, it’s too bad he never answered your reply!

I’ve got a long list of products and the estimate we’ll need to buy varies per product - for example:

• we need 5 t-shirts per employee = Employees * 5


• we need boxes for 25% of our clients = Clients * 25%


• we need 1 epipen per truck = Trucks * 1


• and so on…

I currently have a board with all of the Employee/Client/Truck numbers.

Do you know of any workaround for this? We have almost 200 products, I can’t imagine creating and maintaining a table with so many columns of 1 formula

Hi @Baka_Serkoukou,

Do you have each record marked for what kind of product you need to calculate for?

So, for example, do you have record 1 showing that it needs to calculate for t-shirts, record 2 that it needs to calculate for boxes, etc?

If so, you can still do this all in a single formula field using a SWITCH() function that decides what formula to use based on the product you have the record marked as.

This is assuming you have a field called {Product} that indicates the product for the record…

SWITCH(

   {Product},

   "T-Shirts", {Employees} * 5,

   "Boxes", {Clients} * 0.25,

   "Epipens", {Trucks}

   ...

)

SWITCH(

   {Product},

   "T-Shirts", {Employees} * 5,

   "Boxes", {Clients} * 0.25,

   "Epipens", {Trucks}

   ...

)

Thanks for the quick response!

Ok, I think I understand. It’s would be long because there isn’t a repeated formula “type” per product type.

All 200 products have individual formulas, more or less.

So I’d have to input all 200 products and 200 formulas within Switch() in the single Formula field, right?

yep, that’s the gist of it

For a formula that big, I’d recommend writing the formula out in an external editor – like notepad++ or something, and then copy-pasting it into Airtable’s formula editor. Writing big formulas inside Airtable’s built in formula editor is less than a joy.

I did something kinda similar in a base related to an online class that I teach. I wanted to tally my students’ votes on a few different options, and built a fun system of rollups, lookups, formulas, etc. In the end, I wanted a simple way of displaying the winning choice along with a list of who hadn’t yet voted. The actual data is in some hidden lookup fields, but I use the SWITCH trick to only show the pieces I want on specific lines.

@Baka_Serkoukou - does this work for you?

I’ve created a table of Entities with the number of each:

Then in my Products table, I have a product with its “multiplier per entity”:

i.e. 5 t-shirts per employee, 1 epi-pen per truck

The {Entity} on the products table is a link to the Entity table.

From there I can lookup the {Entity Quantity} and multiply this by the {Multiplier} to get the quantity required:

In essence, taking the formula (or the complexity of the formula) out into a separate reference table.

JB

Awesoooome, thank you so much guys!! My actual situation is soo much more complex, I’ve got keep inventory and purchase 200 items for 7 locations, so I went with the entity table and it makes it possible :grinning_face_with_big_eyes:

@Baka_Serkoukou glad it worked!! So many great answers!

@JonathanBowen really gets to the heart of how powerful relational databases can be.

–

Sidenote: @Jeremy_Oglesby and @Justin_Barrett my new favorite method for making a switch statement is:

1. creating a table that contains a record for each case


2. linking them to a rollup table with a nice array join (,\n)


3. finishing it of with a last string formula to add the ‘switch(case…)’


4. Copy result into formula

Not a bad idea! I think @W_Vann_Hall might even have a post somewhere about using Airtable to help build formulas, though I can’t find the link at the moment.