Currently Being ModeratedJul 4, 2013 7:30 PM (in response to Brevmad84)
This is a big problem statement, and I don't have the opportunity right now to tackle it, but I'll just point you in a direction that may help. Numbers and all other spreadsheets operate on the pull principle, rather than push. Data isn't pushed or pasted into a cell, it is pulled from one or more cells into the cell with the forumla. So, consider what you would write in each cell that needs to display information -- where will you tell it to look for the data.
I hope this helps you to think about how to structure the solution.
Currently Being ModeratedJul 5, 2013 6:19 AM (in response to Brevmad84)
The simplest approach would be to make mirror tables of the master table and apply reorganisation on mirror tables based upon the given conditions.
E.g. Given master table "Table 1", make a mirror table "Table 1-1" with formula such as
A1 =IF(ISBLANK(Table 1 :: A1),"",Table 1 :: A1)
A2 =IF(ISBLANK(Table 1 :: A2),"",Table 1 :: A2)
and in Table 1-1, use Reorganize Panel (Table > Show Reorganize Panel) to show (or hide) certain rows based upon the value of certain column(s). This way, mirror table will reorganise itsef when you edit the master table.
Hope this may help you get the basic concept,
Currently Being ModeratedJul 5, 2013 7:09 AM (in response to Brevmad84)
As Jerry said your description of what you want stand in opposition to the nature of most spreadsheet programs where a cell can compute a value using a combination of other cells. When you use the paste term you are implying an inherent feature Numbers (and other spreadsheets) do not have.
I made an attempt that may takes the "pull" approach to your data and I used three tables on the same sheet for instructive purposes.
I would recommend that you download the free Users Guide Apple provides so you can familiarize yourself with the features Numbers does have:
Specifically how to reference a cell or table.
OK Here is what I can up with (which I don't really think will fully work for you):
There are three tables:
1) a Checking Account named "Checking"
2) a Credit card table (named "CC 1")... the top-right table
3) a Credit card table (named "CC 2")
In column C of the table "Checking" I made a pop-up menu like:
For the top right table (labeled "CC 1") the first two rows are headers.
The formulas are:
A3=IF(B3="", "",OFFSET(Checking :: $A$1,SUM($B$3:B3)-1, 0))
C3="Checking :: C"&SUM($B$2:B2)+1&":C"&ROWS(Checking)
D3=IF(A3>0,DATEVALUE(VLOOKUP(A3, Checking :: A:E, 2)),"")
E3=IFERROR(VLOOKUP(A3, Checking :: A:E, 4), "")
now select A3 through E3 and fill down as needed
Later you can hide columns B and C (and, possibly, the formula in column C directly into column B)
Now in cell D1 enter "CC 1". This sets the transaction type this table "pulls" from the checking table
Now hold the option key while you click and hold on the table "CC 1" then drag to duplicate the table" Rename the table as "CC 2" and change cell D1 to "CC 2"
There is not a great way to allow information you enter OR pull from the table "Checking" so I would submit that you can have three tables per account:
- Summary-- to provide balance of credits and debits
credits for the credit card columns are generally going to come from the checking as you pay the bill. So these tables would work well for that purpose.
I hope this helps
Currently Being ModeratedJul 6, 2013 8:17 AM (in response to Wayne Contello)
Well, you can tell I'm new on these forums. I was trying to say that your reply solved my question, but I accidentally gave the credit to myself.
Oh well. Thanks again for putting that together, sir.
I greatly appreciate it!
Currently Being ModeratedJul 6, 2013 10:50 AM (in response to Wayne Contello)
Ok, so I know that my spreadsheet is a pretty ambitious one for someone who only knows the basics, but I do have a question.
For the "CC 1" table, we made the assumption that all possible credits would be coming from the "Checking" debits table. The formulas you provided work perfectly for that situation. Thanks again!
Would the same basic formulas work on "CC 2" with the added feature that "CC 2" credits could come from either "Checking" or could also come from "CC 1"
(I'm not really going to pay a credit card with a credit card, but let's say I decide to do a balance transfer sometime in the near future to move the "CC 2" balance to the "CC 1" card)
Would it be possible to have "CC 2" looking for "payments" from both "Checking" and "CC 1"? Or to go even further down the rabbit hole, "CC 2" looking for "payments" from potentially unlimited accounts...
Like I said, an ambitious project. I greatly appreciate the assistance you've provided thus far, and if this request makes your brain hurt too much, please don't worry about it. But, if you are able to point me in the right direction to attempt this, I would be extremely grateful.
Thanks a lot!
Currently Being ModeratedJul 7, 2013 9:45 PM (in response to Wayne Contello)
Just thought I would give you an update.
While I'm sure it is possible to create a very complex formula to do what I asked about, it occurred to me that, while more work for the initial setup, creating a table for each account that could pay to another account was the best solution.
I took the table that I made using your formulas and duplicated it, then edited the formula to refer to the other Pay From account.
For example, considering that my checking account could receive money from many different sources, I have 10 different tables that seek out debits from other "accounts" that I have created sheets for. Then the summary table just adds up the amounts from each table for the "Credits" cell.
Now that I have the foundation set up, I'll be able to build it out for the rest of my accounts very easily.
Thanks again for all your help!