ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

Using Excel's INDIRECT Function
~~Donald Reynolds

Excel’s indirect function is one very useful, yet little-known, command that can help solve certain spreadsheet problems by allowing a text string to be used as a cell reference.

Most spreadsheet cells contain numbers, dates, formulas, or text. In general, these different data types can be used in calculations without any difficulty. For example, two dates can be subtracted, added to a cell that contains a number, and then used in a formula. However, a cell containing text that refers to a different cell cannot be directly used in an Excel formula. For example, if cell A1 contains the number 5, then the value in this cell must be referenced by typing “=+A1” (a formula), but not “A1” (a text string). The Indirect function fixes this problem by changing a text cell reference into a format that can be used in a formula.

To see how the Indirect function works, consider the schedule shown in exhibit 1.

Exhibit 1

In rows 12, 13, and 14 are three methods for referencing the sales amount for March. Cell C12 uses the formula =+B6 as a direct reference to March sales, as shown to the right in blue. Cell C13 uses Excel’s vertical lookup function to find March sales in the sales schedule. Finally, cell C14 uses Excel’s Indirect function by referencing the text in cell D6.

Based on this simple example, it seems the Indirect function is more work than the other methods and adds no additional functionality. In some situations, this may be the case. For example, consider the example shown in exhibit 2.

Exhibit 2

In exhibit 2, the Lookup function in cell C13 has been changed slightly from the first example and uses the value in cell D11 as the lookup value. In addition, by using Excel’s concatenation function, cell A13 can also incorporate the value in cell D11. The formula in cell A14 is =” Sales for “&D11&” =”. The end result is a flexible reporting format that only requires the value in cell D11 be changed to report the sales amounts for a particular month.

In this example the Indirect function would be used just as it was in exhibit 1. At this level of reporting complexity, the lookup function is a better choice than the Indirect function.

To see how the Indirect function might be used to accommodate complex reporting needs, consider the trial balance and financial reports shown in exhibits 3 and 4, respectively.

Exhibit 3

Exhibit 4

Exhibit 3 shows a trial balance for ABC Company with an account number, account description, reporting category, and four months of data. The ABC Company Gross Profit Comparison report, shown in Exhibit 4, indicates the general ledger accounts are summarized by reporting category, and two months of data are compared to arrive at a variance amount. The spreadsheet is designed to report on the months entered in cells D3 and D4.

Using lookup functions to complete this schedule is very complex. For example, if the trial balance is on a separate tab named “TB”, the formula for the first month sales (February in this example) using the lookup function would be the following:

=HLOOKUP(B3,TB!D5:G13,2)+HLOOKUP(B3,TB!D5:G13,3)+HLOOKUP(B3,TB!D5:g13,4)+ HLOOKUP(B3,TB!D5:G13,5)

A similar formula would be required for every additional reporting item, such as COGS in this example. In addition, the entire spreadsheet will need to be changed if an additional general ledger account is added, or if the reporting category is changed.

Using the Indirect function and one lookup table, a gross profit report can be created that uses only one formula to determine the reporting amounts, and readily accommodates changes to the trial balance.

To create the gross profit report using the Indirect function, a table will be needed to identify the column on the trial balance tab that is associated with each month’s data. In exhibit 5, this table is shown in green.

Exhibit 5

The next step is to lookup the reporting period column reference in the month lookup table for the two reporting periods. In Exhibit 5, this is shown in blue in cells F3 and F4. The formula is cell F3, for example, is “=Vlookup(D3,H2:I6,2, False).

The final step is to create a formula using the Indirect function to calculate the Sales and COGS amounts. In cell B11, the following formula would be entered:

=SUMIF(TB!$C:$C,$A11,INDIRECT($F$3))

This Sumif formula looks for the reporting category ($A11) in column “C” of the trial balance tab (TB!$C:$C), and then sums all of the values that meet this requirement from the column referenced in the indirect statement.

This formula can be copied down to all reporting categories (only COGS in this example) without changing the formula. For the second reporting column, the Sumif statement must be changed to reference the proper reporting period. In cell C11, the following formula would be entered:

=SUMIF(TB!$C:$C,$A11,INDIRECT($F$4))

Note that only the last character of this formula has changed to reference cell “F4” rather than “F3”. This formula can now be copied to all other reporting categories in column C.

This reporting method allows additional general ledger accounts, additional reporting periods, and changes to the reporting categories to be made without any changes to the formulas.

Although there may be other ways to create this same type of report, the functionality of the often over looked Indirect command should be evident.

Donald Reynolds, DBA, CPA, is a Professor of Business at Calvin College in Grand Rapids, MI.