Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 03, 2019 11:42 AM
How can i make a formula for a single cell?
and how can i show data from cell to cell? for example in google sheet i will right - =B2
Jan 03, 2019 12:14 PM
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.
Apr 17, 2019 12:31 PM
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:
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 :sweat:
Apr 17, 2019 12:39 PM
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}
...
)
Apr 17, 2019 12:51 PM
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?
Apr 17, 2019 02:52 PM
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.
Apr 17, 2019 09:38 PM
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.
Apr 17, 2019 11:24 PM
@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
Apr 18, 2019 02:32 PM
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:
Apr 24, 2019 04:18 PM
@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:
[Maybe a bit overkill, but I avoid the notorious typos]