Welcome to the community, @Emma_Sousa! :grinning_face_with_big_eyes: I just replied to another post of yours, and I’m guessing this one came first. Based on the suggestions I provide here, the advice I gave in the other thread might not apply.
First off, you asked about creating a formula in one table that pulls values from another. Sadly, this isn’t possible. An Airtable formula field can only reference data in the table where it lives, and even more precisely, only the data in the same record. In other words, you can’t arbitrarily reference any other field in any other record, even within the same table. Database records, unlike spreadsheet rows, have no inherent knowledge of other records. Yeah, there are some hacky ways that can sometimes work to get around this, but even they have limitations.
Anyway, that aside, the main thing I recommend is redesigning your base. First, make a table where the primary field contains a combo of the month and year. I used separate {Month} and {Year} fields for the initial data entry, then used this formula in the {Name} field:
Month & " " & Year
Next you’ll have your rIncome] table. Instead of separate columns (fields) for each month, you’ll use a link field pointing to the tSummary] table to pick the relevant month for each income item:
The TBills] table would be set up the same way:
Now the Nov 2019 and Dec 2019 records in Summary] have incoming links:
In that table, add a {Total Income} rollup field that sums the values from those linked items, and set its formatting to Currency:
This gives you a summary of all income, split by month:
Make another rollup named {Total Bills} that does the same with the links in the {Bills} field, then add a formula field that subtracts the difference between the two rollup fields, which will give you this:
Instead of a formula, you can use both the Linked Records and Lookup Table fields. This will link a table to another table, and the lookup field can reference specific data from the linked table. From there you can start referencing the column data via formulas.
If someone else is looking for a way to do this here is the solution I found.
You should create another view of your data. Airtable creates a default "Grid view" for your new table. If you add another view it can act like another table with filtered or grouped parameters. The views all share the same records.
Let me know if that helps!
Instead of a formula, you can use both the Linked Records and Lookup Table fields. This will link a table to another table, and the lookup field can reference specific data from the linked table. From there you can start referencing the column data via formulas.
Lookup Table Fields actually work in referencing values from linked tables and used in formulas. This is a way better and simple approach. Thanks!
