AnsweredAssumed Answered

Conditional formulas

Question asked by RyanHardy on May 2, 2015
Latest reply on May 3, 2015 by 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.

Outcomes