Hello, everybody, welcome to the second dashboard. In this video, we will learn how to use the offset for the dashboard we plan to create. First, I'll just hide these columns. So we can have plenty of space to work with. And I'll be implementing it on these particular cells. So now it's adjacent so it's easy to work with to open this particular formula using the shortcut f2 first and foremost, offset will not work inside indirect.
So it will be outside so I'll start with offset the offset from where should I start. So it should be from the sheet that has been selected by the user from the cell not age whether h1 and that means I'll be starting from h1 the selection that is the sheet name 2016 From there the age column which contains the country. Now, once I'm there, then the second question is how many rows Should I move down. So I do not have to move down. So I'll say 00 is an indicator that do not need to move down. And you may recall, I did mention that zero cannot be allowed for rows and columns.
That is, the height and width of the rows and columns. But for the movement of rows and columns, I can say that it does not have to move down or move to the right. That's why zero rows and zero columns. Now comes the actual question. That is what is the height of the selection? Currently, it's the entire age column.
And that is where I have to limit it should only be interested only select as much of the data as there is. So I'll be using the count a formula because A country is a text not a number. So count a. Again, I would repeat the indirect formula because it is based on the selection that the user makes. So, count a of which column that is the sheet name with h two h column. That means the entire column will be taken into consideration for the counting part and what should be the width the width of the column should be one that means at least one column has to be selected, bracket closed, so, the offset is done.
Now count comes into the picture and where I have to mention what I am looking out for. So, I'm looking for how many times India as a country is getting repeated. So I'll just freeze the a column I'll close the bracket, Enter. And so there it is. So now when I drag it down, I get the same response. And the best part is if I change the number over here, and if you check at the bottom, the time it took earlier, it's not the time that it will be taking.
Because earlier when I used to change the sheet names, it takes more time to rephrase the data and recalculate the whole thing. Because now the data is limited only to the data set that's required unhide and now the data is ready. So as we have seen that earlier, I have used these short namings using the name manager to get my work done faster. So I'll do the same for my account as well because as you can see, offset is quite large. Now in the next video, we'll learn how to use max and min if See you in the next video. Keep practicing.
See you