8 Replies Latest reply: Jul 7, 2013 9:45 PM by Brevmad84
Brevmad84 Level 1 Level 1 (0 points)

I need to know if it is possible to set an IF statement that will essentially copy and paste an entire row's entry into another table when the IF statement is true.

 

I am trying to create a very in depth financial spreadsheet, and the goal of my request is to enter a transaction once, and it shows up in the other account as well.

 

I have my "Checking Account" as one Sheet, and my "Credit Card" as another Sheet. I want to enter a credit card payment into the Checking Account transaction list, and then have it automatically add the transaction to the Credit Card.

 

In the Checking Account Transactions table, I have a Pop-Up Menu created and "Credit Card Payment" as one of the choices. My idea was to use an IF statement somewhat like SUMIF, but not necessarily SUM. This is my thought process, but I'm not sure how to write it for Numbers to perform the action, if it's even possible...

 

IF({Checking Account Sheet}{Transactions Table}{Category Column} = "Credit Card Payment" (as text string from pop-up choice), then copy and paste this row into the next available row in "Credit Card" table)

 

Now, I also have more than one credit card account, so to take it even a step further, in the Checking account transaction, I want to specify a "To Account" within the row for that transaction, and then have it paste to the correct credit card sheet:

 

Checking Account - Sheet 1

Credit Card 1 - Sheet 2

Credit Card 2 - Sheet 3

 

IF({Checking Account Sheet}{Transactions Table}{Category Column} = "Credit Card Payment" (as text string from pop-up choice), then copy and paste this row into the next available row in {sheet that is named "Value of [To Account Column]"}{Transactions Table})

 

or, for a checking account transaction that is a credit card payment to Credit Card 2

 

IF({Checking Account Sheet}{Transactions Table}{Category Column} = "Credit Card Payment" (as text string from pop-up choice), then copy and paste this row into the next available row in {Credit Card 2 Sheet}{Transactions Table})

 

Obviously the Parentheses and brackets and such are not the correct syntax for what I would need to do, hopefully I'm getting across what my intentions are with this request for assistance.

 

Like I said, I'm not even sure that this is possible, but any assistance to help me with this issue would be greatly appreciated!

 

Thanks so much.

 

-Brevig


iWork '09 (Numbers), OS X Mountain Lion (10.8)
  • Jerrold Green1 Level 7 Level 7 (29,855 points)

    Brevig,

     

    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.

     

    Jerry

  • Hiroto Level 5 Level 5 (5,460 points)

    Hello

     

    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,

    H

  • Wayne Contello Level 6 Level 6 (14,925 points)

    Brevig,

     

    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:

     

    http://support.apple.com/manuals/#productivitysoftware

     

    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")

     

     

    Screen Shot 2013-07-05 at 8.31.11 AM.png

     

    In column C of the table "Checking" I made a pop-up menu like:

    Screen Shot 2013-07-05 at 9.04.53 AM.png

     

    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))

    B3=IFERROR(MATCH($D$1,INDIRECT(C3),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:

    - Credits

    - Debits

    - 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

  • Brevmad84 Level 1 Level 1 (0 points)

    Wow. Thanks so much!

     

    This did the trick I was needing to do.

  • Brevmad84 Level 1 Level 1 (0 points)

    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!

     

    -Brevig

  • Wayne Contello Level 6 Level 6 (14,925 points)

    No problem,

     

    glad to have assisted.  Post back if you have other questions.

  • Brevmad84 Level 1 Level 1 (0 points)

    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!

  • Brevmad84 Level 1 Level 1 (0 points)

    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!

     

    -Brevig