Hello, everybody, welcome to the third dashboard. In this video, I've created the final output, this looks something like these. So what I've done is based on the country that I select, say for example us a. The plot also changes if I select a specific field now reflects on the spider web. So I've actually interconnected both of these variables on the data by using multiple filters. So in the macro, I have just added an extra line, I just copy pasted the first line.
And I just changed the job type with country and just added a new variable called user country, which will be taken from the cell from the dashboard, and the rest of the work remains the same. So if I choose all the entire data gets reset and refreshed, and based on the parameter that I select now, the best part over here is that I've used a formula number of records treatment, or recruitment is small over here, just correct it. So a number of recruitments are all used by using concatenate function with a logic. This is a simple concept. That is whatever mentioned in double quotations is given from the user. That's from my end, and the reference is for the cell.
That means whatever user selects over here, automatically, it comes on to the brackets like all so if I select HR, so HR appears. So these are dynamic statements are using the references, data that I want to see and I've added in double quotations. The other calculations are just added across. Just using if statements count a count if and the percentages. So there it is. So based on the selections that I make, automatically, it gives me a relevant report and now that we What we wish to do is when I execute the macros automatically these sheets should not be visible, only the data sheet should be visible to the user and the dashboard.
So I wish that these sheet three sheets should be hidden when the macro is executed. But do remember when the macro executes, it does require these sheets available, so that the macro can get into the changes come out and then present. Now, here we will be recording a small macro when I use any of these buttons automatically. The first thing is, it will unhide the sheets and then make the changes into the sheets and come back to the dashboard and hide them again. For this we'll record a small macro and just name it hide and unhide. So there what we will do is we'll simply right click on all of the sheets and then hide it and then right click on hide it, select the sheet on hide.
So here what we have done is we've actually recorded the process of hiding and unhiding we stop go to macro and then we use a shortcut Alt F 11. So, it will take us directly to the macro sheet and there it is and hide unhide so these are the codes that we need to hide and hide the code is the sheet, the name of the sheet visibility true trues unhide and visibility false that is to unhide. So, let's copy this code go to the dashboard and after assigning of the data to a reference it will paste these codes. So currently the visibility should be on so we will be using the sheet unhide and just remove these for now. So there are total Let's count the number of sheets we want to hide. So 123 and four therefore, she That means code should be written four times two, three and four.
There it is. Another thing that is left is to name them. So I'll just copy the name by double clicking and copying it coming down into the sheet parameter that is under double quotations, and I'll put in the names that I need to hide. So that's one. That's the second one. And the third, okay, this sheet is not needed to be on hid.
So just is the mapping sheet. So keep it hidden as itself. Yeah, this is the one that we need to unhide so there it is, one sheet is not needed. So I'll just delete. Sorry for that. I'll delete this particular code.
So there it is. So it will be unhiding the data for trend chart and we'll be unhiding data For a column chart and we will also be using spiderweb chart. So as we know that the data column that is not needed, but say in case of emergency if we needed to do any kind of changes we can. So once this is done, so once the task is completed, we also wish to hide it. So I'll just paste it once again. But this time in place of true, we say false.
There it is. And, once done, let's close the file. And let's try to execute this. So if I do any changes in HR, automatically the sheets are hidden. If I do any changes over here, let's say India, the sheets are still hidden, USA. There it is.
If I say all In the drop down, I select all yeah. So in that fun, so the user will not be able to see that working. But still he will get the results as needed. Or in case if you are using it for upper management The same goes. In the next video, we will see how to create a trend that will give us a better understanding of the data for the trend chart. Until then, keep practicing.
See you in the next video. Thank you