|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» The Power Combination
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
Combining the SUMIF and OFFSET formulas, a Validation list and a Combo Box to return a summary from data to be selected by month – truly a power combination! Step 1: Selecting items from Validation lists Selecting an item from a Validation list (column A in the screenshot) enables formulas entered into cells in columns C and D to identify the text and return the summary results from a column that contains the criteria (the chosen item) for the month selected in the Combo Box. To add a Validation list to a range of cells: 1. Select the range of cells (in the screenshot on the previous page, the selected cells are A12:A15) 2. Select cells A19:A23. 3. From the Data menu, select Validation. 4. In the Data Validation dialog box, select the Settings tab, and the select List from the Allow box. 5. In the Source box, press F3, select the Name defined for the list (Level3 in this Example, see the screenshot on the last page for this tip), and click OK. Step 2: Entering formulas that return summary balances for chosen items The formula in cells C12:C15 is: =SUMIF(DataLevel3,A12,OFFSET(DataLevel3,0,MonthSelectionNumber+2)) The formula in cells D12:D15 is: =SUMIF(DataLevel3,A12,OFFSET(DataLevel3,0,MonthSelectionNumber+2-12)) Explanation: The SUMIF formula in column C summarizes the balance amounts from the December 2003 column; the SUMIF formula in column D summarizes the balance amounts from the December 2002 column. The SUMIF formula has three arguments: First argument: The range to evaluate according to the criteria entered in the second argument of the SUMIF formula. In this example, the range is DataLevel3, which is the Name defined for column C in the Balance Sheet database. It contains Level3 items of the Balance Sheet, such as Cash, Accounts Receivable, Inventories, and so on. Second argument: The criterion is the item chosen from the Validation list Level3. Third argument: The column from which the data will be summarized. This will be chosen according to the OFFSET formula for the month column, which is adjusted by the number selected from the MonthsList in the Combo Box. The OFFSET formula enables the selected month to be diverted from the base column (column C in the screenshot below). How the OFFSET formula operates Column 29 is the column number for December 2003 and the column number for December 2002 is 17, which is 12 columns before (see the screenshot below). How to change the heading titles in a sheet from characters to numbers: 1. From the Tools menu, select Options. 2. In the General tab, and check R1C1 reference style. When December 2003 is selected from the Combo Box dropdown list (Months List), the month number in that list is 24 (this is calculated by determining the number of months between January 2002 to December 2003: 2 years * 12 months = 24). The linked cell to the Combo Box receives the value of 24. In the data sheet, column 3 is the base column that the SUMIF formula evaluates for the criteria in the second argument of the SUMIF formula. In this case, December 2003 is distanced from column C by 24+ 2 = 26 (2= Column D & Column E). In the third argument, the Sum_range should be 26 columns distant from the base column. The OFFSET formula returns the result of 26 and causes the SUMIF formula to summarize the figures from the December 2003 column. This Tip is taken from the Financial Statements.xls, 2nd Edition book. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| Screenshot // The Power Combination | ||||||||||||||||
       | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Harry Potter and the Order of the Phoenix (Book 5)
Dictionary of Finance and Investment Terms
Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
Preparing the Marketing Plan (Ama Marketing Toolbox Series New Edition)
How to Use Financial Statements: A Guide to Understanding the Numbers
Microsoft Access 2002 for Dummies | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|