When it comes to dashboards in Excel, we rely on the data validation list predominantly to act as user inputs based on which the dashboard is calculated. But this approach doesn't enable the user to multi-select values from drop down nor there is other straight-forward way which makes the life of end user to create such functionality.
So given this case, how do we achieve this?
Well, we can use the form checkbox and a bit of VLOOKUP to get around this and have multi-selection in your dashboard.
Below are the steps which should help you achieve this. Before that lets define our end goal.
Lets say we have to enable to the dashboard to show total sales of fruits by month and the user needs to have a functionality of selecting single/multiple months.
Step1: Below is the data we are working with.
Step2: Lets have a simple table like below which should calculate sales based on dynamic month selection.
Step3: Enable the DEVELOPER tab on the ribbon above. If already enable skip step 3.
Click on 'More Commands' in the 'Customize Quick Access toolbar' menu. Now 'Customize Ribbon'. Select the 'DEVELOPER' on the right-most slide and click OK.
Step4: From the developer tab insert a checkboxes as shown below and name it with the months you have in your data.
Step5: Right click on each of these checkboxes(go to FORMAT CONTROL) and link them to a cell. So that when you CHECK the checkbox the cell should show "TRUE".
Step6: Like shown below. Enter an IF condition to display the relevant month if relevant checkbox is selected adjacent to the linked cell.
=IF(F5,"JAN","")
When you select a month only those months should appear next to linked cells.
Step7: In the data which I mentioned in step1, add a column "MONTH DYNAMIC" and enter the below formula. So that if searches if the month in the line is available next to linked cells. Now apply the formula to all lines in the data. Now you see only months selected are populating in the data.
=IFERROR(VLOOKUP(K4,$G$4:$G$10,1,0),"")
Final step: Enter the below formula in the table shared on step 2. Apply to all rows.
=SUMIFS($M:$M,$N:$N,">""",$L:$L,C8)
Now the table populates data based on which months you select from the checkboxes.
A copy of the file used in the above steps can be downloaded HERE
Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com
Post a Comment
0Comments