File - Send to Excel in Sage: Excel experts needed urgently

File - Send to Excel in Sage: Excel experts...

Didn't find your answer?

I am preparing an article of tips on exporting data from Sage into Excel. Specifically, I am using File - Send Contents to Excel from the "Financials" screen in Line 50 and have exported about 2,000 records into Excel.

A problem is that Sage exports all amounts as positive numbers.

My solution to this is to make a copy of the "Net" column in the worksheet and call this new column "Net2". Then multiply all Net2 amounts which should be credits by minus 1.

Of the 18 Transaction Types ("Tp") in Sage, 9 should be credits. These are BP, CP, PC, PA, PP, SI, PD, VP, JC (= "bank payments", "cash payments" etc).

I can do this the long way by filtering 9 times on the "Tp" field, then multiplying the "Net2" column by -1. But probably some Excel expert out there can tell me how to do it much faster - ideally the whole lot in one go.

The "Tp" column is column B of the worksheet, "Net" in column J, "Net2" would be column K.

Is there some fast easy way? Macro? IF statement? - but the average accountant has to be able to use it.

Your contribution will be acknowledged in the article.

Thanks in advance

David Carter

Replies (24)

Please login or register to join the discussion.

avatar
By mikeopolo
18th Nov 2004 17:38

Blank column has a default text format?
David, if all you get is the formula 'just sitting there', then possibly the column has a default text format, so before you add the formula, format the entire column as general.

Hopefully that will then display the correct answer when you add the tpsign custom formula (nice work Philip!).

Regards

Thanks (0)
avatar
By lornajane
05th Oct 2004 20:49

Recognising dates
I note one of the previous comments mentions that on exporting from Sage into Excel, all dates come in as text.
I get round this routinely by highlighting the date column then using find and replace to swop the slashes for more slashes ie (Ctrl-H) find what / replace with /
Dates are then recognised and can be reformatted or sorted as necessary.

Thanks (0)
avatar
By templar
12th Oct 2004 10:08

Formula Issue
I have been following this one with great interest.
I have gone with Philip Hodgson's formula although I had come up with some of the others myself. Also irritated that I had not thought of Lorna Fellow's solution for dates earlier.

One worry I had: among all the advanced solutions were quite a few formula including lines like:
=A1*(-1)
When
=-A1
would work equally well (and understandable to others).

Just a thought

Simon Maclaren

Thanks (0)
avatar
By ACDWebb
07th Oct 2004 11:07

Philip
I bow to your superior knowledge being in the trade as it were rather than a self taught from a book VBA tinkerer as I am at the end of the day. :-)

Thanks (0)
avatar
By velohead
06th Oct 2004 20:16

VB for Excel - Nice Solutions.....
I always enjoy the VB solutions..

Data Sort By Transaction Type
Highlight Problem Values (presume one column)
Run Macro X
Re-sort if necessary
Job Done !

Macro X is.....
ID start cell (top of highlighted range)
ID finish cell (bottom of highlighted range)
GoTo start Cell
Capture value
Value = [ value *-1 ](ie positive now negative)
Replace New Value
Offsett Down One Cell
Repeat Process
If cell address = finish cell, then stop.
Macro X finished !

Nice and easy.
I use a similiar process to turn "10.00-" into "-10.00" when downloading from my antiquated system.

Not Sure How Many Accounts Know Or Use VB for Excel.
Very Powerful !!
Any Comments ??

Thanks (0)
avatar
By David Carter
07th Oct 2004 22:21

Thanks
Philip, many thanks. In fact I've added your website address to the bottom of the article, if that's OK.
I followed your instructions, and ticked it on my Add-ins list in Excel. But can't get it to work from there.
Presumably I am supposed to create an empty column, then type in =TpSign(B2) say, and a 1 or -1 appears? But nothing seems to happen - the formula just sits there.

Is this because I haven't followed the "Save as an Excel add-in" stuff you mention? Any chance of updating your website so that all the necessary instructions are given there?

Thanks (0)
avatar
By vtsoftware
07th Oct 2004 09:36

Custom function
The line 'Application.Volatile' should NOT be added to the custom function. That is very inefficient as it causes the function to recalculate whenever any data on the worksheet changes.

Without 'Application.Volatile' the function will recalculate only when its input cell changes.

The really cool thing to do is to put the function into a blank workbook and turn it into an add-in. Add-in workbooks are not visible but the functions are still available:

* Click File|Save As and change the 'Save as type' option to 'Microsoft Excel Add-in' (it is at the bottom of the list)

* Close and restart Excel

* Click Tools|Add-Ins and tick your add-in the list (at least a blank workbook must be open or else the command is not available)

The custom function will now be available for all workbooks whenever you start Excel.

I have also made the add-in available for downloading over the Internet at www.vtsoftware.co.uk/tools/vtsage.htm so you do not need to write the function yourself.

Regards
Philip Hodgson
VT Software
[email protected]

Thanks (1)
avatar
By vtsoftware
06th Oct 2004 16:45

Custom worksheet function
A custom function once written gives you the simplest formula on the worksheet and can be used in any workbook. Here's how:

Click Tools|Macro|Visual Basic Editor

In the VB Editor, click Insert|Module

Paste the following code into the module:

Public Function TpSign(ByVal Code As Range) As Long

Select Case Code.Text
Case "BP", "CP", "PC", "PA", "PP", "SI", "PD", "VP", "JC"

TpSign = -1

Case Else

TpSign = 1

End Select

End Function

In the column used to get the sign use the following formula (where the code is in column A):

=TpSign(A1)

Philip Hodgson
VT Software
[email protected]

Thanks (1)
avatar
By ACDWebb
06th Oct 2004 17:28

re Custom Function
you would probably want to insert Application.Volatile at the top to make sure it recalculates automatically.

It would also require someone to go in and edit the code if new codes were added, which a dynamic range named lookup table on a separate sheet would avoid.

You are also potentially going to have to write the code into each new workbook created (unles you store it in PERSONAL.XLS as a general user function. Again it might be easier to have a lookup table pre prepared on a sheet that could be copied into new files.

Thanks (0)
avatar
By CP Fyfe
04th Oct 2004 11:55

lookup table
Put all your types in a table with a value of 1 or -1 in the next column.

Then do a vlookup to put the 1 or -1 next to the data.

Multiply the data by 1 or -1 to get the answer you want.

To tidy up you can hide the column with the original data and the +1/-1.

Thanks (0)
avatar
By carnmores
06th Oct 2004 17:23

My god its a bind
this is why I moved from sage to QB, i remember when sage was simple and fantastic, has ODBC really been in all versions, one point in sages favour they have improved since the competition upped the ante

Thanks (0)
avatar
By carnmores
04th Oct 2004 14:45

Keeping busy!
glad to see you are being even handed, comments are noted re v11 but

there is i believe another problem of a similar nature that you may wish to solve also ( has v11 also corrected this?):

when you have a report on screen and want to email HTML it once again the * - difference is lost, this also seems to happen when you save to a csv file rather than using the file /export method, its very sloppy!

good luck , Nick.

Thanks (0)
Routemaster image
By tom123
04th Oct 2004 10:13

Use Nominal Activity?
Having discovered the send to excel feature following one of your tips, I came across the same problem.

I tend to use nominal activity instead, which gives one debit and one credit column as well as lots of other rubbish...

I create a third column, which is Dr-Cr. Run the formula all the way down, then copy and paste values only. This gives + - numbers. I then delete all the extraneous columns, and proceed to pivot tables.

Send to excel from financials also gives dates as text etc.

Keep up the information flow, very helpful to us users!

Regards

Thanks (0)
avatar
By User deleted
04th Oct 2004 10:14

Sage and Excel
Sage L50 version 11 has a module that installs into Excel so that any report in Sage report designer can be run directly into Excel, saving much time and error prone excel coding.

Your tips would still be usefull for people using older versions I guess.

Thanks (0)
avatar
By User deleted
04th Oct 2004 10:42

Using Import External Data
We have successfully used the import external data function within excell to import data from Sage Line 50 into Excell which gives you postive figures for debits and minus figures for credits.

You can set up many different queries to import and saves changing anything once it is in excell.

Thanks (0)
avatar
By AnonymousUser
01st Oct 2004 19:32

OR and IF
You will need a couple of extras columns or combine the following into a single column.
1) establish if the tp field is a credit..
IF(OR(TpCol="BP",TpCol="CP",..,TpCol="JC"),"Credit","Debit"))
2) Generate Net based on the result....
If(Column from above = "Credit",NetColumn*-1,NetColumn)
The formulae can be reused but there is an not sure if this is the best way to go.
hope this helps Karl

Thanks (0)
avatar
By listerramjet
01st Oct 2004 21:02

substitution
you need to substitute -1 for the known credit types, and a +1 for the remainder in a simple multiplication formula.

given that you have a column with the tp codes, and you know which are credits, you could create a small table of two columns with all the tp codes, and -1 against the credits and +1 against the debits. you could then use a vlookup against the tp column to get the required multiplier, as part of a multiplication formula.

I you email me a spreadsheet with a sample of data, I will happily email back a solution using this method.

Thanks (0)
avatar
By David Carter
01st Oct 2004 22:59

Thanks Alastair
But you haven't given your email. Mine is [email protected] Please get in touch and I will send you the Excel file by return.

Thanks

Thanks (0)
avatar
By AnonymousUser
06th Oct 2004 13:30

Try a simple array!
How about the following using an array, it is simpler to read than emdedded IFs and easier than a macro.

In effect a more elegant version of what Karl originally suggested.

Assumes the Type is in cell B7 and the value is in K7

=IF(OR(B7={"BP","CP","PC","PA","PP","SI","PD","VP","JC"}),-1,1)*K7


However for large lists the VLOOKUP method is better.

Regards,

Rob Bunce

Thanks (0)
avatar
By ACDWebb
04th Oct 2004 11:54

Or even
On a separate sheet have a lookup table 3 columns wide by n rows and in those list

Column 1 BP, CP, PC, PA, PP, SI, PD, VP, JC
Column 2 narrative that explains the entry "bank payments", "cash payments" etc
Column 3 1 or -1 as necessary

Create a flexible named range by selecting Insert : Name : Define
and setting:
Name as TP_Details
Refers to type the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),3)
and click Add

This assumes that your table above is in columns A:C and will create a variable size range name 3 columns wide and n rows high defined by the COUNTA entry

You can now use the following formula to fix the entry from the net column J

=Jn*VLOOKUP(Bn,TP_Details,3,FALSE)

where n is the relevant row number ie in K2 use the formula
=J2*VLOOKUP(B2,TP_Details,3,FALSE)

I have not included a check for NA. Leaving that out will throw up any new TP details which can be added to the bottom of the TP_Details range on the separate sheet and will automatically fix the NA error as the range will expand to include the new entry as you make it.

Thanks (0)
avatar
By carnmores
05th Oct 2004 12:34

OK
this is one of the things i dislike intensely about Sage, they sell you a product that is clearly deficient in a number of ways, export,csv HTML and then they have the gall to charge you for an upgrade which they claim will fix the problems, but often leaves us with a raft of others, hubris.

Thanks (0)
avatar
By vtsoftware
08th Oct 2004 07:33

Custom function
David,

Yes, you are welcome to quote the web address.

I assume you are referring to the add-in you have downloaded, and not one you have made yourself. The instructions on the web page are complete. The Save As stuff is only if you make your own add-in. Yes, you are correct about how to use it.

I know it sounds silly, but have you double checked that the add-in is ticked (not just listed) in the Add-Ins dialog? If it was not ticked when you typed your formula, you will need to retype it.

If it is ticked and you still have a problem, select a blank cell and click Insert|Function and then choose the user-defined category. Does TpSign appear?

If you are still stuck, please email your test workbook to me at [email protected]

I have re-tested downloading the add-in myself and all is OK.

Thanks (0)
avatar
By AnonymousUser
05th Oct 2004 13:22

ODBC
Just wanted to say that the ODBC feature that has been available in ALL versions of Sage allows you to produce spreadsheets in a much simpler way than exporting it and would have not caused the problem you faced with having to amend the data in any way. It can automatically be refreshed to get the up to date figures at the press of a button.

It is a totally unused facility but brilliant for extracting information from Sage which you cant get easily any other way!!

In Excel, run Data, get external data,new database query and take it from there..

Thanks (0)
avatar
By David Carter
05th Oct 2004 12:11

re: Sage export article
Thanks for all your suggestions, chaps. I'm going to go for the lookup table solution because it's the most brainless.

Version 11 of Sage is supposed to be in the post to me at the moment. I'll have a look at all the points you've raised.

Regards

Thanks (0)