Excel Integration – Consolidation of worksheets (Management Reports) using Excel Consolidation
Excel Integration – Consolidation of worksheets (Management Reports) using Excel Consolidation
This document explains how to open a Sage Management Report in Excel using Sage Excel Integration and then using an Excel Consolidation function to create a consolidated management reports
Excel Integration:
Log into Sage and go to File > Microsoft Integration > Excel Integration
This will open Excel on the PC in question with a new Sage tab
Click on the Sage tab and click on ‘Select Company’ drop down
Because this is the first time that Excel is connecting to Sage there will be no companies listed. To add a company to Excel click Add Company.
The Select Company screen will appear, select the company that you want to add to Excel and click OK.
The login screen will appear for you to enter in your Sage 50 user details. At this Stage if you are logged in already as this user close Sage. If you have a new user setup for Excel Reporting you can use this user to log into Sage via Excel.
To pull in a report to Excel select the drop down of Sage Reports and click on Profit and Loss for example. Make sure that you have selected the first cell that you want the report to start at in my case its Line 1 Cell A. Click Insert
If you want to pull in a different Profit and loss report onto a different worksheet for consolidation, open the next Sheet tab in excel and on the top of the Sage tab make sure that you click on the ‘Log Off’ button so that you can then select the drop down of the Recent Companies field to either Add a new company or select your next company if they have already been added to Excel.
For the excel consolidation to work it is advised to add your management reports to each new work sheet, the tabs can be called KBH June 18, TB June 18, KSM June 2018 for example. The 4 tab can be called Consolidation June 18 and it is on this worksheet that you will be consolidating the 3 tabs.
How to merge two reports on different worksheets into one.
This is a simple Excel formula in my example I will just be merging two reports ie Profit and Loss reports into the one report as a Consolidation
In Excel use Sage Excel integration to pull out the Profit and Loss using the same COA and date range onto work sheet 1
On work sheet 2 pull the profit and loss from the 2nd company using the same COA and date range.
The consolidated report will be on Worksheet 3
How to Consolidate:
Remove all headers from the 2 reports on Sheet 1 and 2 so that you have the following:
:
|
On worksheet 3 where the consolidate report will site run the consolidation for example |
||||
 |
||||
|
Because we want to pull in the totals from both sheets select Sum in the Function field as seen below
The Reference box is the range that you want to consolidate. So on Sheet 1 select the range that you want to consolidate and then click Add to include it in ‘All References. Do the same again for sheet 2 by selecting the range that you want to consolidate and then click Add.
Under the section Use Labels in, select the Top Row and Left Columns, if you don’t select them it won’t pull in the headers or the Profit and Loss Categories in column A. Once you are happy select OK to consolidate.
Note: Once consolidate it will correctly consolidate both reports under the correct Profit and Loss Categories however, it won’t highlight the categories so you will need to do this yourself
https://www.ablebits.com/office-addins-blog/2015/09/01/consolidate-excel-merge-sheets/ |
||||
