Excel tips: how to use the INDIRECT function
At a glance
Monthly reports that involve opening and closing balances commonly have a separate sheet for each month. An issue with this method is extracting the closing balance from the previous sheet as the opening balance for the current sheet.
This is typically handled manually in one of two ways: create a new formula linking to the previous sheet and copy it down; or use Find and Replace to correct the sheet name being used in the opening balance formulas.
There is a technique that allows you to copy the current month sheet and rename it, so that all the opening balance formulas automatically update. This technique combines various features and functions. It is advanced and my companion video goes into more detail about all the formulas and techniques used.
This technique requires each month’s sheet to be identically laid out and the sheet name to follow a naming convention.
Worked example
The example is based on measuring monthly head count. The example is a small report, but the technique can be applied to larger reports.
Examine the report in Figure 1.
When you create a new sheet for August 2018 you typically must amend the formulas in column E, so that it links to the previous sheet’s closing balance from column I. The formulas in this example update as soon as you change the sheet name.
The technique relies on a rarely used function called INDIRECT.
INDIRECT function
This function automates calculations that otherwise are only possible with manual intervention. Excel converts the text between the function brackets into either a cell reference, a range reference or even a range name reference. This means you can create references to other sheets using text in cells.
Excel currently has no built-in formula functionality to refer to the previous sheet. By combining INDIRECT and a table we can create such functionality.
Formatted tables
The article "Understanding Excel's misunderstood 'Format as Table' icon" from the August 2018 issue of INTHEBLACK covered formatted tables and we will use one in this example. You may have noticed the Month Table sheet in Figure 1. Figure 2 shows the table in the Month Table sheet. It is named tblMonths.
The first month in the file is July 2018, so there is no previous sheet for that month. This table allows us to identify the name of the previous sheet. The Previous column has the text version of the date. It is left aligned, which means it is text, not a date. The formula in cell B2 is:
=IF(TEXT(A2,"mmm-yy")="Jul-18","None",TEXT(EDATE(A2,-1), "mmm-yy"))
This formula displays “None” for Jul-18. Each subsequent month displays the text of the previous month.
The TEXT function converts a date into a text version of the date using the format between the quotation marks.
The EDATE function adjusts date by a number of months. Using -1 in EDATE returns the same date in the previous month. All dates in column A are the first of the month.
The naming convention used for month sheet names is mmm-yy.
Workings
Columns A to C were hidden in Figure 1. Those columns are used for workings. Figure 3 shows the hidden columns.
Column B (yellow cells) contains the opening balances for the file. The Jul-18 sheet will use those values as its opening balances. Every other month sheet will use the INDIRECT function to extract the opening balance from the previous month’s sheet closing balance (column I in Figure 1).
Sheetname
The formula in cell B1 returns the full path of the file. The current sheet name is listed at the end of the result. The formula in cell B1 is:
=CELL("filename",B1)
You may have noticed that the formula refers to itself. This doesn’t cause a circular reference because the CELL function is an information function, not a calculation function. There are three things to note about the CELL function.
- Always refer to a cell in the second argument. The argument is optional but omitting it may return unreliable results.
- It only provides the full path once the file has been saved.
- It is a volatile function; it calculates each time Excel does.
The formula in cell B2 is:
=RIGHT(B1,LEN(B1)-SEARCH("]",B1))
This formula extracts all the text after the right square bracket in cell B1.
Cell B3 creates a date for the current month from the text in cell B2. The formula in cell B3 is:
=("1-"&B2)*1
This creates a text date with a d-mmm-yy structure. This is recognised by Excel as a date. Multiplying a recognised text date by one converts it into a real date.
Cell B4 holds the previous sheet name. The formula is:
=VLOOKUP(B3,tblMonths,2,0)
The VLOOKUP function looks up the current date in the tblMonths table from Figure 2 and returns the text of the previous month.
Before we can look at the formula in cell E7 from Figure 1 we need to understand something about formulas that refer to other sheets.
Sheet names with spaces
Spaces in sheet names can cause a slight difference in cell references, Figure 4 demonstrates the difference. The sheet Month Table has a space in it. The reference at the top of Figure 4 refers to cell C1 in the sheet.
Notice the two apostrophes around the sheet name. When I remove the space the reference changes and the apostrophes disappear – see bottom of Figure 4. This small change makes a huge difference when creating INDIRECT functions that refer to other sheets.
When creating references to other sheets with INDIRECT you must always include the apostrophes around any sheet names.
Opening balance
The formula in cell E7 of Figure 1 has been copied down and it extracts the closing balance from the previous sheet. Note the “&” symbol joins text together.
=IF($B$4="None",B7,INDIRECT("'"&$B$4&"'!"&CELL("address",I7)))
The IF function handles the first month (Jul-18), which uses column B for its opening balances. The INDIRECT function handles every other month.
Let’s review it in isolation:
INDIRECT("'"&$B$4&"'!"&CELL("address",I7))
The entries between the brackets create a text string to reference cell I7 in the previous sheet. The CELL function with “address” returns the cell reference $I$7.
To demonstrate what is happening I have copied the Jul-18 sheet and renamed it Aug-18. In this new sheet when I select all the text between the INDIRECT brackets and press the F9 function key, Excel displays the result. See Figure 5.
In the Aug-18 sheet INDIRECT converts "'Jul-18'!$I$7" into a cell reference that links to the closing balance from the previous sheet. Copying the sheet and renaming it has automatically established the correct link.
Limitations
As mentioned, this technique requires all the sheets to be identically laid out. This means if you need to insert another department you would need to insert it in to all the other months’ sheets, even the old ones.
If you had a major change you could recreate a new starting month sheet and use that as the new template going forward, using column B for starting balances.
To change all the month sheets in one step you can use Excel’s sheet grouping feature. The companion video will demonstrate sheet grouping, which allows you to edit multiple sheets at once.
INDIRECT function drawbacks
- It is a volatile function, which means that it calculates every time Excel calculates. This can impact the calculation speed of your model.
- It works like no other function.
- It is difficult to audit.
- Most users are unaware of INDIRECT.
However, the advantages of INDIRECT far exceed these drawbacks. INDIRECT can perform many calculations that are impossible with Excel’s other functions. In the above example we have created a way to refer to the previous month sheet: functionality that isn’t built in to Excel.
Helper cells
The formulas in cells B1:B4 in Figure 3 make it easier to create the formula in cell C7. The common term for these types of cells is “helper cells”. I could have created a single formula in cell E7 without the helper cells, but it would have been extremely long and complex. Formulas in separate cells, are easier to understand and maintain.
The companion video and Excel files (example and completed) will go into more detail to demonstrate these techniques.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]
