2 Replies Latest reply on May 3, 2015 2:47 AM by RyanHardy

    Conditional formulas

    RyanHardy

      Title

      Conditional formulas

      Post

      Hello everybody,

      Such a nice forum here.

      I´ll explain what I cannot do so maybe you could help me.

      The thing is that I have the bank statement (excel spreadsheet downloaded from the bank) with the following columns (table 1):

             
      • Date.
      •      
      • Description.
      •      
      • Amount.

      And I have another table with the account number that is related to the description with the following columns (table 2):

             
      • Account number.
      •      
      • Description.

      And another table (it  could be in the same table as in the first one) that has to be populated automatically with the following conditions:

             
      • Account number: The condition is that if in the Table 1, for example contains the text "DHL", then the account number will be, for example, the number 198 (that´s because in the table 2 the account number 198 is the company "DHL services".
      •      
      • Description: It´ll be a text combo with the condition that if  in the table 1 the amount is negative, the text combo will be "PAYMENT from" + the description associated to the before´s account number. And if the amount is positive then text combo will be "INCOME from" + description of the account number.

      For example in the excel spreadsheet that I download from the bank I will have this:

                                                                                                                                                             
      DateDescriptionAmount
      12-01-158976 DHL SERVICE-198,98 €

      In the table 2 I´ll have this:

                                                                                                                                                                                   
      Account numberDescription
      198DHL SERVICES EEUU
      199INTEGRATED SERVICES, LTD.

      So in the third table is where the magic should happen:

                                                                                                                                                                                   
      Account numberDescription
      Check if in the first table in the field exists DHL, if so the account number will be 198As the account number is 198 and it´s a negative amount, then this field will be "PAYMENT FROM DHL SERVICES EEUU"
        

      Sorry about too much explanation, I´ve tried several times to make it work but I´m not able to perform the calculations.

      Thanks a lot for your time guys and have a nice day.

        • 1. Re: Conditional formulas
          TKnTexas

          I would use the Case Statement.  It allows multiple TEST to the data, to return very specific results.   If your test shows a payment sent or received that is NEW, your test would reflect NEW.  Then you can add it to the other table, re-evaluate the transaction to get the account number.

          • 2. Re: Conditional formulas
            RyanHardy

            Thanks for the reply TKnTexas,

            But how can I retrieve the account number? I mean how can I make the formula to check if the column contains the "DHL" text for example and then retrieve the account number?

            Thanks a lot for your time TKnTexas.

            Cheers.