Indirect Function; Ten Ways Among Others to Use It
Parameters of Indirect
Traditionally speaking, the Indirect function has two different parameters, which are the reference text, and then an a1.
Reference Text: This is the reference to a specific cell that is containing a reference of the A1-style. There is an R1C1 style that is referencing a name that has been defined to be a reference, or the reference to a cell, which works as a text string. If it happens that the ref_text is understood to be an invalid cell reference, then the value would return with #REF!, which means there is an error in the value.
A1: This is a logical value that will easily specify the kind of reference that has been contained in that cell known as ref_text. If this has been set to be true, then the ref_text would be interpreted as a reference of the a1 style. However, if it is false, then it would be understood as a reference of the R1C1 style.
Example 1: Using Simple Indirect Function
This example elaborates the possibilities of using the indirect function by knowing the column that we would like to get its value. We’d have a note that the value we are looking for is in A4, but that is all about what we’d know about the information. We will therefore use the indirect function to find the value that is in the A4 cell.
Example 2: Using Sum and Indirect Function
In this example, we are going to use the sum function in combination to the indirect function. We have a number of values that we would like to add all together, and then use the indirect to find a specific number. In this function, we wants to know the indirect information that add the total of a range.
Example 3: Using Average with Indirect Function
We have different numbers that we would like to know the average of. In this example, our company would like to know the performance of the company on an average scale. It is important to us, because the business will have a solid average performance. The average information of people’s performance in sales is what we are going after, and since we don’t know the average, we would also like to find the average, and then use it to find an indirect value. It is solely about finding a solid and working information that would indirectly help us to know the average performance of the company.
Example 4: Additionally Sum and Indirect Function Usage
In this example, we’d have multiple numbers that we want to sum up, but we also want to use the indirect function to fully comprehend what it is actually about. This example clarifies that it is possible to use the sum with indirect function. However, it might be necessary to make sure that the formula would require having the numbers directly from top of the column, and then have the other number in another column.
Example 5: Indirect Function with Text
In this example, the indirect function is going to be used in a way that is similar to the previous example. However, this time we will be only using the indirect function, without the sum function, and still get desired information.
Example 6: Sum and Indirect Extended
In this example, we have a business sales for the past months, and we would like to use the indirect function to know the amount between a staring month and an ending month. We want to know how the business has performed within this period of time, and with the help of indirect function, it is possible to perform the duty at ease.
Example 7: Finding a Specific Value with Indirect & Row Function
In this example, we have set out different data about the business. There is a data for the sales made on a monthly basis, how many people the company has provided its service for on that period, and even how much the company has made for that specific period. We have different data for all these, and we would like to know the data that we have sold for the June month.
Example 8: Indirectly Finding a Number in a Column
In this example, we have the information in all the columns, but we are looking for a specific number in that row. The best way to make this is by having the data ready, and have it ready for the successfulness of the function. In this example, we are going to use the indirect function to find a specific number in the column. This example is about making it possible to know what the business has made for launching a specific product “FA4.”
Example 9: Knowing Sales with SUM and INDIRECT Function
In this example, we have a business that we have layout in all the areas of the business. These areas are the things that keep the business clean. These areas have been outlaid as data, and they all have different contributions to the business. With all these data, we are looking for something that directly make the business knowing its position in all aspects. It becomes possible for the business to know how it is performing. The business name is Ultimate Provider, and focus on providing broadband and telephone services to the business. Now, we want to know how many sales we have made, and with both sum and indirect function, we can successfully do it.
Example 10: Finding Sales from another Spreadsheet
In this example, we’d have our business data on a different sheet, and we are trying to find the right data. With the help of indirect function, we will be able to find the value that we are looking for. This example is a follow-up to the previous example, so if you would like to confirm result of the information, you could look on top, and find what D7 shows.
Template
Further reading: Basic concepts Getting started with Excel Cell References
