Situatie
Solutie
Basic Examples
In this example, typing
=INDIRECT(A1)
into cell B2 turns cell A1 into a reference to cell D1. This is why the result is 5.
In this example, typing
=SUM(INDIRECT(A2))
into cell B2 first turns A2 into a cell reference, and then sums the cells in that reference. This is because the INDIRECT function is embedded within the SUM function.
=INDIRECT(A3)
into cell B3.
However, we can make this much easier by using a named reference. For example, if we were to rename cell A1 of Sheet2 TOTAL, we could use this within our INDIRECT reference. This is particularly handy if you’re creating a reference to another sheet, as it helps you avoid having to remember the exclamation point in the syntax. Also, if your sheet name changes, the INDIRECT reference will not adapt to this change, so using a named reference is a more secure and permanent alternative.
Real-world Example 1
Now that we understand how INDIRECT works, let’s explore how we can use it to make our worksheets tick in the real world. As is often the case in Excel, there is more than one way to achieve the same outcome. For example, you could include the TAKE function to make the following process even more dynamic. However, our aim here is to exemplify the use of the INDIRECT function, so that you can then go ahead and use it in your own way.
Here, we’re tracking the number of goals a football team scores in each game, and we want to work out the average for the previous three games.
First, we will use the COUNT function to work out how many games the team has played so far. Typing
=COUNT(B:B)
into cell E1 will count the total number of cells in column B containing numbers, thus telling us how many games have been played. If you want to count text as well as numbers, use the COUNTA function instead.
Now, we’re ready to use the INDIRECT function to turn the data in cell E1 into a reference. At this point, our aim is to find the average of the values in cells B6 to B8. Here’s how the INDIRECT function will help us to do this. Start by typing
=AVERAGE(INDIRECT
in cell E2, because we’re telling Excel that we ultimately want to find an average, and we’re also using the INDIRECT function to tell Excel where to look to calculate this average.
The first cell we will reference in our AVERAGE calculation is B8, so we will add
=AVERAGE(INDIRECT("B"&E1)
=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1)
to our formula means that Excel will create a cell reference that starts in column B, and then minuses 1 from the value in E1.
Finally, we want to reference cell B6, which is two cells upwards from B8 in our AVERAGE calculation, and then add the closing parenthesis:
=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1),INDIRECT("B"&E1-2))
This correctly calculates the average of 3, 1, and 1, which is the total number of goals scored in the last three games.
Now, when we add more data into column B, the value in cell E1 will increase, as the COUNT function recognizes that there are more values in column B, and the INDIRECT references will update accordingly to always capture the last three values in our AVERAGE formula. All this happens without having to change the formula we typed into cell E2.
Real-world Example 2
We want Excel to tell us how much money each of the four individuals made in either week 1 or week 2, with the option to change that week number if needed.
To begin, we need to name cells B1 to C5 as “week_1,” and cells B7 to B11 as “week_2.” Then, in cell G2, we will type
=VLOOKUP(E2,INDIRECT("week_"&F2),2,0)
because we want Excel to look up Tom’s value in the range we named “week_1,” and return the corresponding value in the second column of that range as an exact match.
We can then use the fill handle to complete the data for the other three individuals, knowing that the references we created are both relative to their respective rows and also secure due to our use of named references.
Then, any changes to the values in columns C or F would automatically apply to the formula we have just written.
Leave A Comment?