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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
