# Make Formula for a single cell

#1

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

#2

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.

#3

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

1 Like
#4

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}
...
)
``````
2 Likes
#5

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?

#6

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.

1 Like
#7

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.

#8

@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

2 Likes
#9

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

3 Likes
#10

@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

[Maybe a bit overkill, but I avoid the notorious typos]

3 Likes
#11

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.

1 Like