Excel: help with my standing orders
An increasing number of clients are paying by standing order and as such keying the information in Sage is becoming a tiresome task. I would love to be able to download my bank statements and import the standing order data into a spreadsheet (no problem so far) but which would then covert the particulars on the bank statement into the appropriate client code for Sage.
E.g. bank statement says Joe Bloggs would like to return a value in excel of BLO100B
Many thanks
Mark
Recurring entries
Thanks, I had considered that but wished to avoid as it is not uncommon for clients to miss a month or, becaues of weekends, some month end entries creeping into the next month.
Vlookup is one option
If you can rely on the same description appearing on the bank statement each month, set up a table listing the bank descriptions and in the next column the Sage codes.
You can then use the Vlookup function to convert the codes. Something like =VLOOKUP([reference to name from bank statement], [Reference to table range],2,false).
Make sure the table range reference is absolute. 2 is the table column number for the answer, false ensures an exact match is found.
Additionally
For anyone interested.
My bank statements did not return the same description each month but I found that by applying the first respondant on this previous post http://www.accountingweb.co.uk/item/94871 I was able to work round it.
Mark
Direct Debit
I asked my bank about setting up direct debit facility and they told me I was too small!
Direct Debits which Bank
I have also tried to change from Standing orders (approx 100 per month). My Bank (Barclays)said I was too small.
Would be very interested if anyone knows of a Bank that will entertain DD's for smaller enterprises.
I have looked at various third party online companies that will process DD's for a fee but I am not sure that I want someone else billing my customers. I would want to retain control of that myself.
Recurring Entry
I set up a recurring entry in SAGE at the same time as the bank mandate that way you dont have to post the bank entries but you also have a double check that a standing order has been paid. Having saud that we have now almot moved away from standing orders to Direct Debit as its easier to administer and change prices! Its not as expensive as you might think!