I am working on a home budgeting spreadsheet and have effectively dwindled everything down to a couple of sheets. What I would like to do is have the main table (1) of a sheet be used for expense data entry. Then I would like for another table (2) to (1) and if certain data appears in a particular column cell, (2) would then show a predetermined formula. My real-world example is this. Table-One (1) has an expense entry from my credit card. Column E represents “Categories. If any expense row contains the category “FOOD”; I then want the Debit amount for that expense row to be deducted from the corresponding category balance of Table-Two (2). Table-Two (2) represents balances budgeted for a period. So essentially I want Numbers to automate the calculation of my expenses as they chip away at categories I am spending in. This way I can visually see as I enter them, how much is left in my budget for the period. Each sheet will represent a resource, checking account, credit card, etc.
—–
Christopher
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I Create a Formula In One Table, Referencing Text & Numbers In Another?
Comments: One Response to “How Do I Create a Formula In One Table, Referencing Text & Numbers In Another?”
Comments Closed.
Referencing cells in other tables works the same was as referencing the cells in the same table. But it is far easier to do if you don't try to type the cell's identifier. Instead, click on the cell. So if you want B1 to be the sum or A1 and G5 from another table, then type =B1+ and then instead of G5 you click on cell G5 in the other table to reference it.
But this doesn't help you with calculations like you are suggesting.
Instead, you want to use SUMIF. Look at the description of that function carefully. You can have SUMIF add up all of the expenses in one table where the the category matches that of a cell in the second table.
So in table 2 if A2 is the name of the category ("Food") and B2 is the budget, say $500. Then in table 1 if column D is the amount and column E is the category, then you could use this in C2 of the second table:
=B2-SUMIF(Table 1::A,A1,Table 2::B)
So start with the amount to spend (B2) and subtract the sum of all of the values in column B of the first table where column A is equal to value A1 of the second table. Just click on the cells (and row letter headings) instead of typing any cell references to get it right.