INDIRECT: The Dark side of Excel
INDIRECT Cheat Sheet
.xlsx
Download XLSX • 15KB
Has your manager ever told you the tragedy of Darth Indirect the wise? I thought not. It's not a story the CPAs would tell you...
At the beginning of my career in finance, I used INDIRECT in a lot. It was so easy: once it clicked for me, I thought I had unlimited power. I felt like I just learned the cheat code to Excel. But INDIRECT couldn't quench my thirst for power. I went further and downloaded an add-in that added a new function to my version of Excel 2010, called Indirect.Ext, which allowed my to indirectly reference a cell in a closed file anywhere on the network. No file was safe from my reach.
This went on for about a year. I kept feeding INDIRECT more and more of my worksheets, and it kept growing stronger and stronger. However, as my files grew, I noticed INDIRECT started leaching their power, slowing them down to the point of inoperability. I began to realize, I had to cut INDIRECT out of my life by chopping off its limbs and letting it burn in a lava river.
Ok, that is a bit extreme, but the moral of the story is the same: Don't overuse INDIRECT. I found that I could have eliminated my need for INDIRECT by structuring my worksheets better or by using Power Query (though it wasn't yet released at that time). Over the years, I've learned good and bad use cases for INDIRECT, I'll share some of those with you today.
Fundamentals of INDIRECT
If you've never heard of INDIRECT, conceptualizing how it works can take some time. Every formula that you've used in Excel up to this point has used direct references, for example, =A1 or =SUM(B2:B4). With INDIRECT, we take those direct references out of the picture and instead refer to them indirectly, essentially building references out of information from:
other cells
text
results of other formulas
Of course, you can also combine any number of these sources to build a reference with INDIRECT using the & symbol. See some examples in the gif below.
The trick to using INDIRECT correctly is to first know what your reference would look like if it was a normal, direct reference, and then start to replace the reference with your inputs. Take another example: =SUM(A1:A3). How would we replace this reference with INDIRECT? We have a couple good options.
Write "A1:A3" in a separate cell (let's say C1), and reference that cell like so: =SUM(INDIRECT(C1)). This works just fine, but what if you wanted to be able to quickly change the start cell and end cell?
Write "A1" in one cell (let's say C2), and "A3" in another (C3). Combining these cells to get back to our formula takes a bit more space: =SUM(INDIRECT(C2&":"&C3)).
Note: In order to build these references, you'll have to be familiar with using quotes (") and ampersands (&) to combine text and cell references. Any static text goes in quotes, any direct cell references don't. You concatenate all text and cell references using the ampersand. Any cell references wrapped in quotes inside INDIRECT will be taken as the reference to that cell ( =INDIRECT("A1") will return contents of A1).
Referring to Other Sheets
Using INDIRECT with sheet references is where I've seen others get tripped up the most. Here you have to be extra careful with the syntax, as sheet names throw in some extra punctuation to the mix. Let's take an example of two sheets, one being "Other Sheet", and "Sheet2". The direct references to A1 in each sheet is as follows:
- "Other Sheet": 'Other Sheet'!A1
- "Sheet2": Sheet2!A1
Notice that sheet names with spaces are wrapped in apostrophes, which make both direct and indirect references a little more lengthy.
Here's a quick reference on how you might build an indirect reference to each A1 in both sheets:
Other Sheet:
Cells | B1 = Other Sheet B2 = A1 | B1 = ''Other Sheet' B2 =A1 | B1 = ''Other Sheet'! B2 =A1 |
Indirect | =INDIRECT("'"&B1&"'!"&B2) | =INDIRECT(B1&"!"&B2) | =INDIRECT(B1&B2) |
Sheet2:
Cells | B1 = Sheet2 B2 = A1 | B1 = ''Sheet2' B2 =A1 | B1 = Sheet2! B2 =A1 |
Indirect | =INDIRECT(B1&"!"&B2) | =INDIRECT(B1&"!"&B2) | =INDIRECT(B1&B2) |
Take note that the sheet references will require two apostrophes in front when entered into a cell for INDIRECT. This is to to the fact that Excel takes the first apostrophe entered into a cell as a cue that what's entered after is text, and basically removes it from the cell. The cell will still display as 'Other Sheet', however, the true contents of the cell are actually ''Other Sheet'. Play around with that!
Sheet2 and 'Sheet2' are both acceptable, but both will still need an ! at the end. Because of the space in Other Sheet, apostrophes are always required.
Learning on Your Own
I could go on and on with examples of how to build these types of formulas, but the process honestly is quite straight forward:
Create your formula without any use of INDIRECT.
Determine which pieces you want to be dynamic (sheet names, table names, cell references, named ranges), and start to cut out and replace those with cell references or text.
Troubleshoot if your formula isn't performing as intended. This is a large part of the learning process for INDIRECT!
Give it a shot! Open up some of your most used spreadsheets and try to replace some of the formulas with INDIRECT, and let the power flow through your sheets...
