Contents
Congratulations! You made it to the final day of the marathon 30 Excel functions in 30 days. It has been a long and interesting journey during which you have learned many useful things about Excel functions.
On the 30th day of the marathon, we will devote the study of the function INDIRECT (INDIRECT), which returns the link specified by the text string. With this function, you can create dependent drop-down lists. For example, when selecting a country from a dropdown list determines which options will appear in the city dropdown list.
So, let’s take a closer look at the theoretical part of the function INDIRECT (INDIRECT) and explore practical examples of its application. If you have additional information or examples, please share them in the comments.
Function 30: INDIRECT
Function INDIRECT (INDIRECT) returns the link specified by the text string.
How can you use the INDIRECT function?
Since the function INDIRECT (INDIRECT) returns a link given by a text string, you can use it to:
- Create a non-shifting initial link.
- Create a reference to a static named range.
- Create a link using sheet, row, and column information.
- Create a non-shifting array of numbers.
Syntax INDIRECT (INDIRECT)
Function INDIRECT (INDIRECT) has the following syntax:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) is the text of the link.
- a1 – if equal to TRUE (TRUE) or not specified, then the style of the link will be used A1; and if FALSE (FALSE), then the style R1C1.
Traps INDIRECT (INDIRECT)
- Function INDIRECT (INDIRECT) is recalculated whenever the values in the Excel worksheet change. This can greatly slow down your workbook if the function is used in many formulas.
- If the function INDIRECT (INDIRECT) creates a link to another Excel workbook, that workbook must be open or the formula will report an error #REF! (#LINK!).
- If the function INDIRECT (INDIRECT) references a range that exceeds the row and column limit, the formula will report an error #REF! (#LINK!).
- Function INDIRECT (INDIRECT) cannot reference a dynamic named range.
Example 1: Create a non-shifting initial link
In the first example, columns C and E contain the same numbers, their sums calculated using the function SUM (SUM) are also the same. However, the formulas are slightly different. In cell C8, the formula is:
=SUM(C2:C7)
=СУММ(C2:C7)
In cell E8, the function INDIRECT (INDIRECT) creates a link to the starting cell E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
If you insert a row at the top of the sheet and add the value for January (Jan), then the amount in column C will not change. The formula will change, reacting to the addition of a line:
=SUM(C3:C8)
=СУММ(C3:C8)
However, the function INDIRECT (INDIRECT) fixes E2 as the start cell, so January is automatically included in the calculation of column E totals. The end cell has changed, but the start cell has not been affected.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Example 2: Link to a static named range
Function INDIRECT (INDIRECT) can create a reference to a named range. In this example, the blue cells make up the range NumList. In addition, a dynamic range is also created from the values in column B NumListDyn, depending on the number of numbers in this column.
The sum for both ranges can be calculated by simply giving its name as an argument to the function SUM (SUM), as you can see in cells E3 and E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Instead of typing a range name into a function SUM (SUM), You can refer to the name written in one of the cells of the worksheet. For example, if the name NumList is written in cell D7, then the formula in cell E7 will be like this:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Unfortunately the function INDIRECT (INDIRECT) cannot create a dynamic range reference, so when you copy this formula down into cell E8, you will get an error #REF! (#LINK!).
Example 3: Create a link using sheet, row, and column information
You can easily create a link based on the row and column numbers, as well as using the value FALSE (FALSE) for the second function argument INDIRECT (INDIRECT). This is how the style link is created R1C1. In this example, we additionally added the sheet name to the link – ‘MyLinks’!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Example 4: Create a non-shifting array of numbers
Sometimes you need to use an array of numbers in Excel formulas. In the following example, we want to average the 3 largest numbers in column B. The numbers can be entered into a formula, as is done in cell D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
If you need a larger array, then you are unlikely to want to enter all the numbers in the formula. The second option is to use the function ROW (ROW), as done in the array formula entered in cell D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
The third option is to use the function ROW (STRING) along with INDIRECT (INDIRECT), as done with the array formula in cell D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
The result for all 3 formulas will be the same:
However, if rows are inserted at the top of the sheet, the second formula will return an incorrect result due to the fact that the references in the formula will change along with the row shift. Now, instead of the average of the three largest numbers, the formula returns the average of the 3rd, 4th, and 5th largest numbers.
Using functions INDIRECT (INDIRECT), the third formula keeps the correct row references and continues to show the correct result.