true

Learn Microsoft Excel Training from the Best Tutors

• Affordable fees
• 1-1 or Group class
• Flexible Timings
• Verified Tutors

Search in

# Use of Indirect Function

Kaustubh Chavan
20/05/2019 0 0
I would like to share my experience of how Indirect formula helped me in my recent project. I was working with some report which was connected to a large database in excel, so whatever changes I made to the large data set, the changes were reflected in my report. It was something like auto-report kind of thing but not exactly an auto-report. I will show you how exactly the auto report looked like so will get the fair idea of the report.

Above is the sheet which gets updated from the data set, the way I bring the figures from the large sheet is simply by using sumif, the sumif function sums up all the figures belonging to the product mentioned in this present sheet in Column A starting from row 6 i.e A6. This sheet shows the total amount for the product in terms of Target, Current year sales, and Last year Sales. One of the drawbacks of SumIf formula was its rigidity, It wasn’t flexible, the moment the sheet from which it draws the data for the purpose of the total is removed or I have to connect to some other large data set with a similar format but different figures in the case for comparison.I had to reframe my SumIf formula in order to get the name of the sheet in my formula and hence I had to re-write the formula again, it became a cumbersome job to re-write the formula again. Hence to avoid this trouble I thought of using Indirect Function.
The Indirect Function returns the reference from the range, so while using SumIf formula I used the Indirect formula that would grab the name of the sheet mentioned in some cell or range. So even if I have to connect to the different sheet I just have to change the name in the range or cell which the Indirect formula will indirectly refer and avoid the task to reframe the formula to match with the sheet name.
So the general SumIf formula goes in the following manner
SUMIF(range, criteria, [sum_range])
Range: List of products.
Criteria: Specific Product in your Sheet where you want the total amount to appear.
Sum_Range: The figures which you want the club to form the total for the specific criteria.
So when you are getting the data from another sheet but obvious the formula will show the sheet you are referring to but unfortunately, it is static, that it won’t change if we happen to connect the report to some other sheet, In the fashion has given below
=SUMIF('All India'!\$H\$3:\$H\$1277,'All India Products'!\$A6,'All India'!\$AX\$3:\$AX\$1277)

The name of the sheet is highlighted in a bold letter which won’t change because it is static.
Range: \$H\$3:\$H\$1277

Criteria: \$A6( In current sheet where the formula is being typed)

Sum_Range: \$AX\$3:\$AX\$1277

But if I write the same formula using Indirect function I can bring dynamism to the formula and make it flexible which would avoid time wastage in re-writing the formula.
So here is the improved version of the formula,

=SUMIF(INDIRECT("'"&\$A\$3&"'!\$H\$3:\$H\$3908"),\$A7,INDIRECT("'"&\$A\$3&"'!\$DK\$3:\$DK\$3908"))

In this formula A3 is the cell or range where I have mentioned the name of the sheet, so the formula takes the sheet name from this cell i.e A3 in the present sheet where you want the figures to appear, next most important thing is the single quote,kindly see the static version of the formula where the sheet names are mentioned in bold letters. The name of the sheet is bounded by single quotes, Yes they are equally important in our dynamic formula too. So in order to grab them and make Excel understand that those single quotes are to be taken as single quotes, they are enclosed by double quotes, so that they are accepted in their literal form and attached to the cell reference with the & symbol which grab the name of the sheet in similar fashion given below.

“ ‘ “ & \$A\$3 & “ ‘ ! \$H\$3:\$H\$3908, I used spaces deliberately so that readers can read each and every input properly so that there won’t be any typing error or things get missed out while typing.

0 Dislike

## Other Lessons for You

Tips - How to put PivotTable Field List back at its Original Position?
Have You ever struggled to put PivotTable Field List back at its Original Position?*Original Position - right-side of the worksheet, as highlighted in the following picture: If Your answer is Yes, You...

MS Excel Tips
If you want to do a save as on MS Excel Workbook, use the shortcut key F12 and then select the location and click save
R

Excel Conditional Formatting
In this video, learn about Conditional Formatting, Creating Conditional Formatting Rules, Use Conditional Formatting Presets, Managing & Removing Conditional Formatting.

How to Remove Duplicates from your data
Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a organization and only want to see the number of organization you have or you have multiple rows of same type...

How To Create Cutom List And Its Advantages?
You must have observed when you are typing Month names, Excel suggests the next Month Name. Also, you can drag the cursor using the anchor at the right bottom of cell and the series of month...

### Looking for Microsoft Excel Training classes?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you

X

### Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

• Select the best Tutor
• Book & Attend a Free Demo
• Pay and start Learning

### Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro