Welcome to the community, @Sarah_Tilley! :grinning_face_with_big_eyes: Because Airtable’s data storage is more like a database than a spreadsheet, rows (records) have no inherent knowledge of each other. While it’s technically possible to link records together in the same table and use lookup/rollup fields to pull from the linked records, in this situation it ultimately leads to a circular relationship, which Airtable won’t allow.
Aside from some really messy multi-table solutions (which I never took the time to fully delve into because…well, they’re really messy), the only way to pull this off inside Airtable is with the help of a script. This could be triggered manually by putting the script in a Scripting block, or possibly using an automation, though the latter would require some very carefully planning to keep the automation’s changes from triggering further changes in a chain reaction. Personally, I’d play it safe and use the Scripting block.
If you’d like help with either option, just give a shout!
