Contents
At first glance (especially when reading the help), the function INDIRECT (INDIRECT) looks simple and even unnecessary. Its essence is to turn text that looks like a link into a full-fledged link. Those. if we need to refer to cell A1, then we can either habitually make a direct link (enter an equal sign in D1, click on A1 and press Enter), or we can use INDIRECT for the same purpose:
Please note that the function argument – a reference to A1 – is entered in quotation marks, so that, in fact, is text here.
“Well, OK,” you say. “And what’s the benefit?”
But do not judge by the first impression – it is deceptive. This feature can help you out in a lot of situations.
Example 1. Transpose
A classic of the genre: you need to turn the vertical dia
groove to horizontal (transpose). Of course, you can use a special insert or function TRANSP (TRANSPOSE) in an array formula, but you can get by with our INDIRECT:
The logic is simple: to get the address of the next cell, we glue the letter “A” with the special character “&” and the column number of the current cell, which the function gives us COLUMN (COLUMN).
The reverse procedure is better done a little differently. Since this time we need to form a link to cells B2, C2, D2, etc., it is more convenient to use the R1C1 link mode instead of the classic “sea battle”. In this mode, our cells will differ only in the column number: B2=R1C2, C2=R1C3, D2=R1C4 etc.
This is where the second optional function argument comes in. INDIRECT. If it is equal LYING (FALSE), then you can set the link address in R1C1 mode. So we can easily transpose the horizontal range back to vertical:
Example 2. Sum by interval
We have already analyzed one way of summing over a window (range) of a given size on a sheet using the function DISPOSAL (OFFSET). A similar problem can also be solved using INDIRECT. If we need to summarize data only from a certain range-period, then we can glue it from pieces and then turn it into a full-fledged link, which we can insert inside the function SUM (SUM):
Example 3. Smart table dropdown list
Sometimes Microsoft Excel doesn’t treat smart table names and columns as full links. So, for example, when trying to create a drop-down list (tab Data – Data Validation) based on column Employees from smart table People we will get an error:
If we “wrap” the link with our function INDIRECT, then Excel will easily accept it and our drop-down list will be dynamically updated when adding new employees to the end of the smart table:
Example 4. Unbreakable Links
As you know, Excel automatically corrects reference addresses in formulas when inserting or deleting row-columns on a sheet. In most cases, this is correct and convenient, but not always. Let’s say that we need to transfer the names from the employee directory to the report:
If you put regular links (enter =B2 in the first green cell and copy it down), then when you delete, for example, Dasha, we will get the #LINK! error in the green cell corresponding to her. (#REF!). In the case of using the function to create links INDIRECT there will be no such problem.
Example 5: Collecting data from multiple sheets
Suppose we have 5 sheets with reports of the same type from different employees (Mikhail, Elena, Ivan, Sergey, Dmitry):
Let us assume that the shape, size, position and sequence of goods and months in all tables are the same – only the numbers differ.
You can collect data from all sheets (do not sum it up, but put it under each other in a “pile”) with just one formula:
As you can see, the idea is the same: we glue the link to the desired cell of the given sheet, and INDIRECT turns it into a “live”. For convenience, above the table, I added the letters of the columns (B,C,D), and on the right – the line numbers that need to be taken from each sheet.
Pitfalls
If you are using INDIRECT (INDIRECT) you need to remember about its weaknesses:
- If you link to another file (by gluing the file name in square brackets, the sheet name, and the cell address), then it only works while the original file is open. If we close it, we get the error #LINK!
- INDIRECT cannot refer to a dynamic named range. On static – no problem.
- INDIRECT is a volatile or “volatile” function, i.e. it is recalculated for any change in any cell of the sheet, and not just influencing cells, as in normal functions. This has a bad effect on performance and it is better not to get carried away with large INDIRECT tables.
- How to create a dynamic range with auto-sizing
- Summing over a range-window on a sheet with the OFFSET function