What Is the Indirect Function?
The indirect function is a way to convert a text string into a reference. That is, it draws information from a reference to another cell or range. It creates a reference from text, and doesn’t change when cells, rows, or columns are altered, added, or removed from a cited range. The references it creates are evaluated in real-time, so the reference is always accurate to the data it’s drawing from. If that feels a little confusing, don’t fret. The indirect formula can be easier to understand with valid examples and in practice. If in doubt, have a go following the steps below, and you’ll soon get the hang of it.
Using the Indirect Function With Named Ranges
Named ranges in Excel are a great way to collect data under a single reference, and the indirect function makes grabbing that information from them that bit easier. Here’s how to do it:
Using the Indirect Function on Multiple Sheets
The indirect formula is even more powerful when you use it to pull information from other sheets. You don’t need to use named ranges to do it, either. =SUM(INDIRECT(G5) In our example, we can replace Burgers in cell G5 with Lemonade or Desserts, the other two named ranges, and the output will change to their SUM total instead.
Using the Indirect Function With R1C1 Style Reference
For sheets that are continuously expanding, where the reference you want to use won’t always be in the same cell, R1C1 Style references can be used with the indirect formula to give you the information you need. We’ll continue to use our food sales examples here, but imagine it’s for a higher level worksheet that looks at weekly sales totals overall. =SUM(INDIRECT(B4&"!B4:B10")) =INDIRECT(“R12C”&COUNTA(12:12),FALSE)
