0I 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.
Kindly write feedback/Comment on this article.
