Hello, everybody, and welcome back to the second dashboard. In this video, we will see the implementation of indirect that we learned in the previous video. A one cell will be the dummy cell, which we will be using for the user input. And so I'll just add in 2012. For now, I'll just name it user input year. So that in the future if I wish to change this reference, I can do it on the formatting Name Manager.
And from there, I can change the reference as per my needs and requirements. As we have already mentioned many of the referencing names that we are using in some of the other formulas. So if in the future, I prefer to change this from data for dashboard to the actual dashboard, I could simply change the reference and the formulas refer to this particular name will refer to the dashboard directly. Now, let's get started. In the earlier video, we did see indirect is used to indirectly refer to a particular cell or a reference. And in this example, I'll be using the reference to take it to the sheet directly.
Say for example, if I select this particular cell address this that is a one, it should take me to the sheet 2012 with the same reference, and it goes beyond the other data. And so it is that so what we're going to do is wherever there is 2012, we'll substitute it with the reference. So first, let's start with indirect. Then we put in a double quotation as the other variables are all provided by Excel. In this current example, it is something that needs to be exact, including the quotations as well. So the same quote will come into the double quotation then I'll put an ampersand to connect the variables.
And I'll just refer to this cell directly that is user input data. Again, open the quotation Repeat the same task till the end. That's one of the functions for the first variable which is average the first criteria is range and the second criteria is a four that is something the formula itself will pick up India. Now, the third variable that we wish to have is the same reference now, we do not have to redo the entire formula, simply we will be copying the indirect function until a percent and pasting it in place to 2012 and that should work okay so the double quotation and exclamation and the rest of the formula remains as it is close the quotation and we had to close the bracket and that's an indicator that indirect function is also complete. The violet bracket is for the indirect the red one is for average and the black one is for if error.
Once done, press enter and see whether the answers are there. Now as we can see in 2012, there were No data from any of the countries. Now, if I change the sheet 2013 I should get the relevant answer. And there it is, as the year changes to some other reference like 2014, my data does reflect the resources from this respective sheet. Now, if I put in a year which is not existing say 2020, automatically the entire frame gets converted back to zero as it is giving an error which triggers an IF error to substitute all the errors into zero. Now, let's bring back the data.
And there it is. Now, that's how the reference will be done. So you can also enter this formula into the name box directly but the only condition is it is applicable only for the active sheet. That is something that we will be doing in the upcoming videos to fetch the data entirely from another sheet. In the next video, we will see how to calculate the number of employees using the same concept So and keep practicing. See you in the next video.
Thank you