How to Refer to a Cell in a Previous Row in Airtable
If you are moving a spreadsheet workflow into Airtable, you may want each transaction to compare itself with the one before it. For example, an expense table could show how many days passed between one transaction and the previous transaction.
Airtable formulas cannot read a cell from the row above. To make this work, link each transaction to its previous transaction, look up the previous date, and then use a formula to calculate the difference.
I’ll use an expense table throughout this example. Each expense has a Date, a Sequence, and a Previous Transaction field.
Create the fields for the previous record
In the expenses table, add these fields:
| Field | Type | Purpose |
|---|---|---|
| Date | Date | The date of the expense |
| Sequence | Number | The order of the expense |
| Previous Transaction | Link to another record | The expense immediately before this one |
| Previous Date | Lookup | The date from the linked expense |
| Days Since Previous | Formula | The number of days between the two expenses |
The Sequence field gives Airtable a stable order. A date alone is not enough when two expenses happen on the same day. If you need to maintain a custom order, see how to reorder records in an Airtable grid view.
Link the previous transaction manually
If you only have a few expenses, enter the links yourself. Sort the table by Sequence, with the smallest number first. Leave Previous Transaction empty for the first expense, link the second expense to the first, and continue down the list.
To create the field, add a Link to another record field and choose the same table. This creates a self-link, so one expense can point to the expense before it.
Open the Previous Date field and set it as a lookup of Date through Previous Transaction. Each expense will then show the date from the record it is linked to.
Calculate the difference with a formula
Create a formula field called Days Since Previous and use:
IF({Previous Date}, DATETIME_DIFF({Date}, {Previous Date}, "days"))
The first expense stays blank because it has no previous transaction. If the current expense is dated May 14 and the previous one is dated May 6, the result is 8.
Link new transactions automatically
If new expenses are added regularly, an automation can create the link for you. The automation needs a reliable order, so use Sequence rather than relying on the position of records in a grid view.
Create an automation with When a record is created as the trigger. Select the expenses table.
Add a Find records action that searches the same table. Set the condition so Sequence is less than the Sequence value from the triggering record. Make the condition dynamic and insert the Sequence value from the trigger.
The action may find several earlier expenses. Let Find records return the matching records, then add a Sort list action and sort them by Sequence in descending order. Use the first record returned after sorting. If the table has more than 1,000 earlier records, adjust the action's record limit or use a narrower condition.
Add an Update record action. Use the record ID from the trigger as the record to update, then put the record ID found in the previous step into Previous Transaction.
When a new expense is added, Airtable finds the earlier expense with the highest Sequence number and links it to the new one. The lookup and formula fields then show the previous date and the number of days between the two expenses.
Check the order before using the automation
The automation links a new record when it is created. It does not rebuild every existing link if you add an older expense later or change a Sequence number.
If two records use the same Sequence number, Airtable cannot know which one should come first. Give every expense a unique Sequence value and test the automation with the first record, a later record, and an expense added out of order.
For more examples of linking records with an automation, see how to automatically link records between Airtable tables. For date differences that use two fields in the same record, see Airtable’s DATETIME_DIFF documentation.