If/then formula that uses the value from a single cell

I need to write a formula that uses a specific cell of data as a multiplier:
IF({BoM Category}=“3.1-Infusion Ingredients”,({Formula %}*SPECIFIC CELL OF DATA),IF({BoM Category}=“4-Product Ingredients”,{Formula %},0))

Do I need to create an automation to do this? If so, what are the steps?

Can you share a screen shot clarifying what you mean by {SPECIFIC CELL OF DATA}?

If it is a specific cell for a specific field for a specific record, you cannot do this the way you would in a spreadsheet. Airtable formulas cannot reference cell values in other records.

There may be some workarounds to get the value of that {SPECIFIC CELL OF DATA} into all records. For example, if it is a static value, you can hardcode it into the formula or into a different formula field. If you have linked record fields you can link all records to the record with the value and pull the value across using a lookup/rollup field.

You might also move the value from the original cell to an editable field in every record that needs it using an automation or a script. But a single automation run is limited in the ability to update multiple records. Updating multiple records usually requires multiple automation runs or a script.

Once you get {SPECIFIC CELL OF DATA} into another field in the record, you can try something like this (assuming that {SPECIFIC CELL OF DATA} is a number field.

SWITCH( {BoM Category},
    "3.1-Infusion Ingredients", {Formula %} * {SPECIFIC CELL OF DATA},
    "4-Product Ingredients", {Formula %},
    0
)

Thanks for your help! I actually figured it out. Appreciate your assistance!

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.