 |
|
|
|
|
|
Consolidation is the process of combining data from separate worksheets into one worksheet so you can perform calculations on it. For example, lets say you have three spreadsheets each set up to record your expenses over the last three years and you wish to find out the total, average, maximum or minimum figures of your data. Consolidating the three worksheets into one is the way to do it. Consolidated data can be shown either in the workbook, in a pivot table (works great)
The attached workbooks above contain three worksheets containing expenses for the past three years and one worksheet where we will consolidate our data. Note the tabs are named Consolidate, 2007, 2008 and 2009.
The first thing you need to do is highlight the area in which you intend to consolidate the data. So, on the Consolidate worksheet, highlight the area B4:M10. Now on the Data Tab, under Connection Tools, select the Consolidate option. (If you are using 2003, go to Data>Consolidate. Note at the top of the dialog, under Function the default is Sum. Click the drop-down arrow and you will see that there are many more calculations you could glean from your consolidated data, Sum is just the most common, and therefore the default and calculation we will use in our example.
Click in the Reference: area and then select the collapse* tool at the far right of the input area. Now click on the 2007 worksheet and select the range B4:M10, then click the collapse tool again and select the Add button. Now select the collapse tool and navigate to the 2008 worksheet and select exactly the same range. B4:M10 then hit the collapse tool again and again select the Add button. Lastly, do the same thing for the 2009 worksheet, using the same range and selecting the Add button when you have collapsed back through.
Finally, once all three ranges are in the dialog box, select the OK button and you will see the result of consolidating the three worksheet ranges. In effect, Excel layers the data, one on top of the other, then uses the Consolidate feature to calculate the layered data. In this case, we are using the SUM function, but we could have used any of the functions listed in the Function area.
The great thing about Consolidation is that you will see no actual formulas in your consolidated data, just the result of your consolidation. Therefore you would need to save your finished product to retain the final consolidation values.
There may be scenarios where the layout of your worksheets may vary slightly from each other. Excel again takes care of this, by reviewing the content of the ranges automatically and checking the row/column headings before plotting the layout and consolidating your data.
If you are using Excel 2003, another interesting spin you can use with the Consolidate feature is that you can Consolidate data using a Pivot Table. Pivot Tables take up very little space on your PC and can be manipulated further once set up.
To create a Consolidation using a Pivot Table, you need to first ensure you are on the Consolidate worksheet. Now go Data>PivotTable and PivotChart Report, then click on Multiple consolidation ranges, then Next. Now select Window and Window to your workbook containing the sheets to be consolidated. Select the 2007 worksheet and then the range B4:M10, then select Add and add all other ranges as required. Finally, select Next, then Existing worksheet, to place the Pivot Table in you current workbook, then select Finish.
*Note that using the collapse tool does nothing other than shrink the dialog box up to allow you to see and select areas behind it. You actually do not need to collapse the dialog box, you can just select your range, with it showing in full.
This month we will look into the MsgBox Function and more importantly, how to capture which button the user clicked.
Option Explicit
'Message Box Syntax
'MsgBox(prompt[, buttons] [, title] [, helpfile, context])
Sub MsgBox_Static_Title_Msg()
'Move to very top of Module To make it _
available to all Procedures within'
'Use as _
Public Const strTitle As String = "Ozgrid.com" _
At the very top of any Public Module to make _
To all Public and Private Procedures
Const strTitle As String = "Ozgrid.com"
Const strMsg As String = "How are you feeling Today?"
'Run by placing cursor within Procedure & push F5
MsgBox strMsg, vbOKOnly, strTitle
End Sub
Sub MsgBox_Mix_Look()
'We can join looks and buttons by use of +
'Run by placing cursor within Procedure & push F5
MsgBox "Hello Subscribers", vbInformation + vbOKOnly
End Sub
Sub Msgbox_Capure_Reply()
Dim lReply As Long
'Run by placing cursor within Procedure & push F5
lReply = MsgBox("Do you wish to continue.", vbYesNoCancel + vbQuestion)
Select Case lReply
Case vbYes
MsgBox "You chose Yes"
Case vbNo
MsgBox "You chose No"
Case vbCancel
MsgBox "You chose Cancel"
End Select
End Sub
Sub Msgbox_Capure_Reply_Change_Default_Button()
Dim lReply As Long
'Run by placing cursor within Procedure & push F5
lReply = MsgBox("Do you wish to continue.", vbYesNoCancel + vbQuestion + vbDefaultButton3)
Select Case lReply
Case vbYes
MsgBox "You chose Yes"
Case vbNo
MsgBox "You chose No"
Case vbCancel
MsgBox "You chose Cancel"
End Select
End Sub
See ya next month :)
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft