add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Chapter 15, Data, explains the principles and rules for organizing data in tables in your Excel worksheet. It is important to organize your data in your data tables according to these clear, well-defined principles. This will allow you to take advantage of the features Excel offers, such as sorting, automatic filtering and using pivot tables.
In order to prepare reports that are based on data taken from the data tables in your worksheet, remember to use formulas to locate and return data from data tables. Summarize your data according to criteria from within the data tables.
This chapter explains how to use formulas for these tasks, including formulas for locating and returning data. These include Lookup & Reference in the Paste Wizard function, Sumif and Offset functions, and combining functions with combo boxes and validation techniques.
Prepare the data table for use with formulas, assign names and insert an ascending numbered column.
Assign names to the data table and add a column with consecutive numbers
Assign names
Assign names to the following data in the data table: the data table itself and the fields of data (columns). See Chapter 6, Names for an explanation of the importance of defining names.
Define a name for the worksheet in the data table
Create only one data table in the sheet. The first cell in the data table should always be A1. This placement of the data table, which always contains the first active cell (A1), aligns the numbering of rows and columns in the sheet; aligns the row and column numbers in the data table; and prevents the problem of updating the name of the data tables when rows or columns are added to the data table.
Note
Do not assign the Name of the sheet when you are working with a pivot table. To work with a pivot table, only define a Name of the data table.
Define a name for the sheet
Select the sheet by either pressing Ctrl+A or clicking on the button to the left of the worksheet’s heading (to the left of column A, above row 1). Then press Ctrl+F3, enter name into the Names in workbook box, and then click OK.
Define a name only for the data table
Select a cell in the data table, and press Ctrl+*. Then press Ctrl+F3, insert the name of the data table in the Names in workbook box, and click OK.
Define names for fields in the data table
Fields in the data tables refer to columns in the data tables. The field name is the text of the heading cell of the column.
Select a cell in the data table, and press Ctrl+*. Press Ctrl+Shift+F3, or from the Insert menu, select Name, Create. Then select the Create names in top row option, and click OK.
Define a name for a single cell
Select the first cell (heading cell) in one of the fields, and then select the data range in the field (press Ctrl+Shift+Down Arrow). Enter name in the Names in workbook box, and click OK.
Add a first field with ascending consecutive order
The VLookup formula finds a number or text value in the first left column of the table and returns a value from a cell which is in the intersection between the row, which is in column 1, and the column number you set in the data table. Nesting the Match formula in the VLookup formula requires that there be an initial left column in the data table with consecutive, ascending numbering that is automatically updated whenever there is a change in the records, in other words, during Sort.
Insert a first left column in the database. Enter the name of the field of the first cell in the head of the column, which is cell A1 (assuming that you added a column in the top corner of the sheet). In cell A2, enter the formula =ROWS ($A$2:A2). Copy the formula down until the last cell of the data table.
explanation
The Rows formula returns the calculation of the number of the row in a formula cell range from the first cell until the current address. The first cell address is absolute, while the address of the last cell in the range is relative (relative distance).
result
Excel automatically calculates and updates the number of the row. You may sort the table according to any field that you desire, and you may delete or add rows. The result is Excel calculates a new row number and updates it in ascending order.
The Lookup formula group
The Lookup formula group (explained below) returns one value from a cell in the data range or the data table. Note that the Match formula is different and returns the consecutive (ordinal) number of the cell in the cell range and not the value of the cell.
List of Lookup formulas discussed in this chapter:
Lookup
Returns one value from a cell in the range of the corresponding data table.
Hlookup
Returns one value from a cell in the data table, horizontal design.
Vlookup
Returns one value from a cell in the data table, vertical design.
Match
Returns an ordinal number of a cell in the cell range, either horizontal or vertical.
Index
Returns one value from a cell in the data table.
Lookup formula
Select a cell in the sheet, and press Shift+F3. From the Lookup and Reference category, select the Lookup function. Or enter =Lookup in the cell, and then press Ctrl+A (shortcut for displaying the Select Arguments dialog box of the formula).
In the Lookup formula, there are two possible calculations for returning a value from a cell:
© Searching a data range and returning a value from a cell in the same range, or
© Searching a data range and returning a value from a cell in a parallel range.
Returning a value from one data range
Select the second argument in the Select Arguments dialog box (the shorter argument), and click OK.
Example: In order to calculate the Consumer Price Index (CPI) to the date of March 15, 2001, you must first locate the CPI published closest to the date of the computation.
Result: See the list of the dates in the figure. The date closest to the desired date of March 15, 2001 is March 1, 2001.
Calculation method of the Lookup formula
In the Lookup formula,Excel searches for the value March 15, 2001 in the range of cells. The search method is from the end to the beginning (when the date range is sorted from the lowest to the highest, in ascending order). The formula returns a result that is equal to or lower than the value that was checked; in the example here, the value is March 1, 2001.
Note
Be consistent in the type of value you check and the range of cells in which you check for the value (date/dates, number/numbers, etc.).
The Lookup formula in the Function Arguments
The Lookup formula has two arguments. In the first argument box, select a cell that contains the value for which the calculation takes place.
In the second argument box, paste the name for the cell range, or select the cell range (vertical range only) in the worksheet in which you carry out the search.
example
To retrieve text from the list sorted in ascending order, type the text Int in cell C2. Enter the Lookup formula in cell D2.
result
Excel retrieves the text Cisco (see cell A4), which is the first value that is lower (alphabetically) than Int.
The Lookup formula: returning a value from a parallel range
The Lookup formula (see figure) returns the Income for the date March 15, 2001.
Two names are defined in the example:
© Dates – cell range B3:B15
© CPI – cell range C3:C15
Note
The two cell ranges (CPI and Dates) are of equal length (13 cells). The ranges MUST be of equal length in order to obtain correct results.
Enter three arguments in the Lookup formula
1. Select cell E4, type =Lookup and press Ctrl+A. The Function Arguments dialog box appears.
2. In the Function Arguments dialog box, select the first option, and click OK. The Function Arguments dialog box appears.
3. In the first argument, Lookup_Value, select cell E3.
4. In the second argument, Lookup_Vector, click F3, and paste the name Dates.
5. In the third argument, Result_Vector, click F3, and type the name CPI.
6. Finish entering the formula, and click OK.
result
The formula searches (in the Dates range) for a date that is equal or less than March 15, 2001. The date that fulfills this condition is March 1, 2001 (cell B5). The results are returned from the corresponding cell in the Income fields; the value that the formula returns is 120 (cell C5).
The VLookup formula
The VLookup formula returns a value from a cell in one of the table columns. The VLookup formula is an extension of the Lookup formula.
The letter V at the beginning of the formula is an abbreviation for vertical. VLookup searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.
Row: The formula that returns the number of the row in the table is Lookup.
Column: Consecutive number of the column in the table.
Question
What was the Consumer Price Index (CPI) on March 15, 2001?
Answer
The CPI that was known is 120 (see figure).
Explanation
The value of 120 is located in the cell which intersects the row in the data table with a date equal or less than March 15, 2001 (the date that fulfills this criteria is March 1, 2001) and the second column on the data table, which is the CPI column.
Enter the VLookup formula
Step 1: Define a name for the table
1. Select a cell in the data table, select an active area, and press Ctrl+*.
2. Press Ctrl+F3, and enter the name Data in the Names in workbook field.
3. Click OK.
Step 2: Type the formula
1. In cell F2, type the number 2 (the number of the CPI column in the data table).
2. Select cell F5. In the formula bar, type =Vlookup, and press Ctrl+A. The Function Arguments dialog box appears.
3. In the Lookup_value (first) argument box, select cell F3 (the value for which the calculation is made).
4. Select the Table_array (second) argument box, click F3, and paste the name Data.
5. In the Col_Index_num (third) argument box, select F2 (the number of the column in the data table).
6. Click OK.
Results of calculating the formula: The CPI on 3/15/2001 is 120.
explanation
When a Name (or a cell range) of a data table is entered as the second argument of the VLookup formula, Excel calculates the number of the columns in the data table. Remember not to enter a column number that is greater than the number of columns in the data table; this will cause Excel to return an erroneous result.
The fourth argument: Range_Lookup
In the first argument in the VLookup formula, the value that is entered is the value of the row number in the first column in the table.
The default in the fourth argument (Range_lookup) is empty. This means that Excel will look for a value in the first column that is equal or less than the original value.
To find an exact match, enter False in the fourth argument. If you do so, you do not need to sort the data according to column A. The VLookup function looks to find the exact match in the left unsorted column.
The Match formula
The Match formula returns the ordinal number or position (and not the value) of a cell within the range of the cells that are searched.
example
Cell B12 is the tenth cell position within the range of the cells that have been assigned the name called Dates (B3:B15).
Enter the Match formula
The date 3/15/2001 shown in cell E3.
1. Select cell E6.
2. In the formula bar, type =Match and then press Ctrl+A. The Function Argument's dialog box appears.
3. In the Lookup_Value argument box (first), select cell E3.
4. In the Lookup_Array argument box (second), click F3, and paste the name Dates.
5. Click OK.
The third argument box: Match_type
The third argument box validates the first argument’s value within the range that is searched. For example, the value in the first argument’s box is the date March 15, 2001; this date does not appear in the range that was assigned the name of Dates.
Enter 0 to search for an exact match in the Match_type argument. In this example, the result will be the error type #N/A.
The Index formula
The Index formula returns a value corresponding to the intersection of the row number on the data table and the column number in the data table.
Index formula returns one of two possibilities:
© Short formula arguments which return a value from a cell.
© Long formula arguments which return a reference.
Example, see the figure above:
Cell E2 contains the number of the row in the data table.
Cell E6 contains the number of the column in the data table.
The Name of the data table is Data. The result of the calculations is 110 in cell E7.
Boost the clout of your calculations: combine formulas
Combine the VLookup and Match formulas
The VLookup formula returns data from any column you choose in the data table; simply change the number of the column in the third argument.
Although it sounds simple, there is a catch. How can you determine the number of a column in a data table that contains numerous columns? How can you easily change the number of the column in the third argument of the VLookup formula? How can you easily change the column number in multiple formulas in a worksheet from which complex reports are prepared or in a sheet that contains multiple VLookup formulas?
solution
Combine the Match formula with the third argument of the VLookup formula.
Combining the VLookup and Match formulas:
1. Calculate the column number by using the Match formula.
2. Combine the two formulas into one.
In the figure above, the column number of April 2001 is 7.
Calculation - The first three columns in the table are Account Number, Account Name, P&L (profit and loss), plus four columns for the months January-April.
Task - To calculate the column number according to the text heading of the column.
Tip – add new headings to the data table
In cell D1, enter the date 1/1/2001; in cell E1, enter the date 1/2/2001. Select the two cells and drag the fill handle to the right to copy them to the rest of the cells in Row 1. Select all the cells, and press Ctrl+1; the Format Cells dialog box appears. Select the Number tab, and then under Category, select Custom. In the Type box, type mmmm yyyy and click OK.
Step 1: Define 2 names
1. Select Row 1, press Ctrl+F3, type the name Row1 in the Names in workbook field, and click OK.
2. Select the entire sheet by pressing Ctrl+A. Then press Ctrl+F3, enter Data in the Names in workbook field, and click OK.
Step 2: Enter the Match formula
1. Open an adjacent worksheet, and select cell A1.
2. In cell A1 type 4/1/2001.
3. In cell B1, enter the formula =MATCH (A1, Row1, 0).
(Be careful to enter the value 0 in the third argument to specify the search
for an exact value.)
4. Results of calculation: 7.
Step 3: Enter the Vlookup formula
1. Enter the account number 201 into cell A2.
2. Enter the formula =VLOOKUP(A2,Data,B1) in cell B2. In the third argumentof the VLookup formula, select a cell which contains the Match formula.
3. Calculation results: 7,981.
Step 4: Combine the formulas
In the formula bar (Match formula) of cell B1, select the formula without the = sign, press Ctrl+C, and click the Cancel sign (from the left of the formula in the formula bar). Select cell B2, and in the formula bar, select the address B1. Press Ctrl+V and press Enter.
The final result is a nested formula is
=VLOOKUP (A2, Data, MATCH(A1, Row 1, 0))
Combine the Index and Match formulas
Step 1: Define a name
Define a Name to column B. In the example, the Name is ColB.
Step 2: Enter the Match formula
1. Select a new sheet. In cell A1, enter one text from the expenses list.
2. Calculate the row number. In cell B1, enter the formula =MATCH (A1, ColB, 0). The result of the calculation is 9.
3. In cell A2, enter the date 4/1/2001.
4. Calculate the column number. In cell B2, enter the formula =MATCH (A2, Row1, 0). The result of the calculation is 7.
Step 3: Enter the Index formula
In cell C1, enter the formula =INDEX(Data, B1, B2). The result is 345.
Step 4: Combine (nesting) the formulas
Now you will use the technique of copying and pasting a formula from the formula bar (without the = sign) into a different formula.
From the formula bar of cell B1, copy the Match formula without the = (equal) sign. Then click the X to the left of the formula (to cancel); select the C1 cell; in the formula bar, select the B1 address; and press Ctrl+V.
Use the same technique to copy the Match formula from the B2 cell to the formula bar in cell C1 (instead of the B2 address).
The combined formula is (see figure on page 372):
=INDEX(DATA, MATCH(A1, ColB, 0), MATCH(A2, Row 1,0)
Combo Box
You added the Match formula to the VLookup formula in order to calculate the column number.
You added two Match formulas to the Index formula in order to calculate the row and column numbers.
Choosing an item from a list in the Combo box returns a number. The number then replaces the use of the Match formula to calculate the column number in the VLookup formula and replaces the use of the Match formula to calculate the column number in the Index formula.
Where do I find the Combo Box?
Select one of the toolbars and right-click the mouse. From the shortcut menu that appears, select Forms.
Working with the combo box
A list of the cell ranges in the sheet is attached to the Combo Box. After an item is selected from the list, the Combo Box enters into the linked cell the ordinal number of the item that is selected on the list.
Example:
The figure below displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.
Add a combo box
Step 1: Define names
1. Enter the months January-December into the cells A1 to A12 in the new worksheet.
2. Select the list of months, press Ctrl+F3, enter the text MonthsList into the Names In workbook box,and click OK.
3. Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.
Step 2: Add a combo box to a worksheet
1. Select one of the toolbars, right-click, and select the Forms toolbar.
2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.
Step 3: Format the combo box
1. Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab.
2. In the Input range box, type the name MonthsList. (You cannot press F3 to paste a name with an object.)
3. In the Cell link box, type the name MonthNumber.
4. Click to select the 3-D shading box (more aesthetic).
5. Click OK.
Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.
Advantages of working with names in a combo box
Attaching a list with Define Names causes your list references to be updated automatically in the Combo Box. Sorting the list in the sheet will automatically sort the list in Combo Box as well.
Deleting a combo box
Select the object; right-click; and from the shortcut menu that appears, select Cut.
Deleting all the objects in a worksheet
Press F5, click the Special button, select the Objects check box, click OK, and then click Delete.
Adjusting the size of the combo box to the cells
Select the Combo Box and right-click; the Combo Box is now in editing mode. Adjust the width or height of the Combo Box by dragging one of the corner boxes; adjust the placement of the Combo Box on the worksheet by clicking anywhere inside the box and dragging it.
Automatic adjustment of the combo box
Hold down the Alt key while you click the mouse to adjust the size of the Combo Box.
Combine the VLookup formula and the Combo Box
Attach a list of 36 months (starting from January 2001) to the Combo Box.
1. In Row 1, select and copy the list of months. Select cell D1, click the left arrow plus Ctrl+Shift, and press Ctrl+C.
2. Select a new sheet in your workbook. Select cell A1, right-click, and select Paste Special from the shortcut menu.In the Paste Special dialog box, select Transpose. The reason: the input range in the Combo Box must be vertical.
3. To define a Name for the list of months, click Ctrl+F3, enter the name MonthsList in the Names in workbook box, and then click OK.
4. Select cell B1, press Ctrl+F3, enter the name MonthNumber in the Names in workbook box, and then click OK.
5. Select one of the toolbars, right-click, and select Forms.
6. Add a Combo Box to the sheet from the Forms toolbar (see previous instructions).
7. In the Input range box, enter MonthsList. In the Link to cell box, enter MonthNumber. Then click OK. (Note that you cannot paste a name to cells through the F3 shortcut here).
8. Open the Combo Box, and select March 2001.
The result: the number 3 is entered into cell B1, which is named MonthNumber.
Adjust the number of the column in the data table to the number that is linked to the combo box
The number 3 that appears in the MonthNumber cell is the number of the month that you select in the Combo Box. The number corresponding to the March 2001 column (in the data table) is 6: three columns for the Account Number, Account Name, and P&L (Profit and Loss); and three columns for the months January 2001 to March 2001. This number, the column number of March 2001, must be inserted as the third argumentof the VLookup formula.
The VLookup formula is =VLOOKUP (B3,DATA,MonthNumber+3).
Combine the Index formula, Validation and Combo Box
The Combo Box is an excellent, easy-to-use technique that serves us well when we need to calculate the column number. The number in the cell is linked to the Combo Box, and it replaces the use of the Match formula in calculating the column number in the VLookup and Index formulas.
However, the Combo Box does not help us in calculating the number of the row in the Index formula.
Example: Prepare a report of the Expenses list for the month of March 2001.
To calculate the column number, add a Combo Box with a list of months, as explained above. The number in the cell is linked to the Combo Box and matches the columns of the sheet. Excel returns the results of the calculation of the column in the data table, and this, in turn, is the column number in the third argumentin the Index formula.
Calculating the row number – How can you calculate the row number, which is the second argumentof the Index formula? Suppose you try to define a list for Account Name (column B, cell range from B2 until the end of the list), and then attach the list to a Combo Box. You see the problem: you would have to create numerous combo boxes in all of the cells, and in each Combo Box you would select only one account name. This is not a practical solution.
Solution
Attach a Validation list to the cells.
For a detailed explanation of the Validation technique see Chapter 2, Text.
Add a validation list
Step 1: Define a name
Select the Account Name list in column A by selecting cell A2 and pressing Down Arrow+Ctrl+Shift. Press Ctrl+F3, enter the IndexList name in the Names in workbook box, and click OK.
Step 2: Attach a list to the cells
1. Open the worksheet in which you will prepare the report, and select the cells from B5 on.
2. From the Data menu, select Validation.
3. Select the Settings tab, and under Allow, select List.
4. Select the Source box, press F3, paste AccountName, and click OK.
5. Open each validation list in any cell from cell B5 on, and choose the account item.
Step 3: Enter the Index formula
Enter the Index formula in cell D5 (see figure).
Explanation: In the second argumentin the Match formula, use the selection technique from the Validation list in order to easily select an item from the list. The Match formula returns the ordinal number of the text that was selected in the Name column. This is the ColB column in the data table.
Summarize data according to criteria from the data table
The combination of formulas in the Combo Box and Validation returns only one value from the data table.
The SUMIF formula summarizes data according to criteria (see Chapter 8, Summing and Counting). The OFFSET formula enables us to change the reference. The combination of the two formulas and the addition of a Combo Box allow you to easily summarize data according to criteria from the data table.
Example, see figure below
Task: Summarize the Profit and Loss by P&L items (see P&L, column B) for the month of January 2001 or any other month that you choose.
Step 1: Specify a name for column B in the worksheet
Select column B, press Ctrl+F3, enter ColB in the Names in worksheet box, and then click OK.
Step 2: The SUMIF formula
The SUMIF formula summarizes data according to criteria.
see Chapter 8, Summing and Counting.
The formula has 3 arguments
1. First argument – Range – ColB (column B in the data table sheet).
2. Second argument – Criteria – b3 (the text: Revenue).
3. Third argument – Sum_range – D:D (column D, January 2001). This is the data range from which the data-by-criteria will be summarized.
Problem
The SUMIF formula is excellent for summarizing data according to criteria. However, there is a limitation: you cannot make any changes in the summary range when you use the SUMIF formula. In the example above, you summed up the data from the January 2001 column. How, then, can you easily change the range of the sum in order to sum from the column of March or April (instead of from January)?
There is a third argument in the VLookup formula: changing the column number also changes the number of the intersected column. The Index formula is much more flexible; you can change both the number of the row and the number of the column. The SUMIF formula needs help, and the solution is to add the OFFSET formula.
Step 3: The OFFSET formula
The OFFSET formula returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
The formula in the figure above returns a value from a cell that is a given distance from the base cell of B4. The distance is 0 rows, 2 columns. The cell address is D4.
Step 4: Combining the OFFSET and SUMIF formulas
In the combined formula below, the data from January 2001 is summarized according to a criteria.
The formula is =SUMIF(ColB,B3,OFFSET (ColB,0,2))
Changing the third argument in the OFFSET formula will offset (reposition) the data summary range. In order to accomplish this, add a Combo Box to the sheet.
Step 5: Combo box
Add a Combo Box as explained earlier in this chapter. The name of the cell linked to the combo box is MonthNumber.
In the figure below, see the third argument of the SUMIF formula in the formula bar. The name of the cell that is linked to the Combo Box appears here.
Combining SUMIF, OFFSET and a Combo box provides an incredibly powerful tool for querying and summing data from a report.