INDIRECT FUNCTION

The INDIRECT function is cool. When you first look at it, you wonder what the heck you can do with it and then all of a sudden you realize that you have a lot of applications for it.
The INDIRECT function accepts a text string as an argument and then evaluates the text string to determine the relevant cell or range reference. What does that mean?
Let's start with a basic example:


Based on the Excel spreadsheet screenshot:
=Indirect(”A1") Returns the contents of the referenced cell which is B1

=Indirect(A1) Returns the actual contents of the referenced cell. Excel sees that cell A1 contains the cell reference B1 and goes and returns the value in B1 which is 100.
If cell A1 had contained text such as CPA, then CPA would have been returned if quotes had been used.
However, if quotes were not used you would see a #REF! Error since there is no cell reference called CPA.

Okay, so what can you really do with this? Does anyone have an Excel workbook that has a sheet for each month and a summary sheet that displays key calculations for the current month? If not, perhaps you have a file contains sheets by brand or product line and then a summary sheet? If so then you probably spend a lot of time linking or copying and pasting. Using the Indirect function will save you time and allow you to more time to analyze the data.


In the example below, I have a summary sheet that tracks the current month volume in both dollars.

The supporting sheets with the store information are labeled by month - Jan, Feb and March.

 Instead of linking or pasting numbers into summary sheet cells B6 each month I can automate the process by using the Indirect function. Notice that cell B4 is the cell showing the month’s key values that I am displaying. In this case January.

 The formula I use to retrieve the total shipment dollars shipped in January is

This formula tells Excel go look at cell B4 and to find the cell or range reference found there. In this case Excel looks for JAN which is a sheet name. The ampersand joins the month name with the cell reference of G19. Excel goes to the January sheet and returns the value found in G19 to this summary sheet. If I wanted to see the value of G19 on the February sheet all I have to do is change the name in B4 to Feb to match the name of the actual sheet.

Excel goes over to the Jan sheet and then retrieves the value of 2,556,375 at cell G19 and returns it to the summary sheet.







This example was a bit simple – what happens if every month has a different number of rows or isn’t nicely totaled? If the column had not been totaled I could have used a formula such as this

or if you had no idea how many rows were being populated you could have substituted G:G for G1:G40.
To make it more efficient, use a data validation drop-down list of months in B4.