Sage Accpac Intelligence - MS Excel Tips & Tricks #1
Posted by Plus Consultants on Thu, Dec 08, 2011 @ 08:30 AM
Sage Accpac Intelligence - MS Excel Tips & Tricks #1 – Working with Worksheets
The demand for Sage Accpac Intelligence (“SAI”) as a reporting tool creates a need for more advanced MS-Excel skills. SAI generates data that is then output to Excel. This raw data is manipulated and configured in Excel. Because of this, the “creator” in Excel must have intermediate to advanced Excel ability to produce meaningful, legible reports. These reports may use such features as: grouping worksheets; charts; pivot tables; conditional formatting; pick lists; named ranges; etc. This article will focus on working with worksheets, namely, grouping worksheets.
Grouping Worksheets
By grouping worksheets, you can apply formatting and formulae to the multiple worksheets. This helps save time and provides consistency across worksheets.
You must ensure that the area in which you want to make modifications across the grouped worksheets is set out in the same format.
You can select a range of worksheets by selecting the first worksheet, holding the SHIFT key down and selecting the last worksheet of the range. You can select non-adjacent worksheets by using the CTRL key and select the pertinent worksheets. You can de-select by choosing a worksheet outside the range (ie. Not previously selected). Furthermore, you can select ALL worksheets by right-clicking on any worksheet tab and choosing Select All Sheets. Conversely, to de-select, right-click on any worksheet tab and choose Un-group sheets.
The following workbook has 2 worksheets: Vancouver and Calgary. They are formatted the same with these headers: Cust #; Invoice #; Amount
We now want to insert a column for the Invoice Date. First of all, GROUP the worksheet. Then, highlight Column C. Now when you hit “Insert”, a column will be inserted into BOTH worksheets:
When you type the column heading “Date”, it is reflected on BOTH worksheets. Formatting is replicated as well, whether it be fonts, fills, number formats, etc. This is a very useful tool when you are trying to create multiple, consistent reports. From this you can then start considering creating consolidated reports!
Subscribe to our blog for more Accpac Tips & Tricks from the experts or contact us for more info!