Multiple Use Radio or Checkmark Buttons

ScottTemple

Board Regular
Joined
Dec 28, 2023
Messages
103
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a workbook that is pulling data in from a Power Query, that I am creating a Pivot Table from. I would like to create a third tab in the workbook to create an interactive dashboard, without using VBA/Macros. What I am attempting to create is that when I select the 'All Shavers' checkbox, data is pulled from the pivot table to encompass all the different Shaver numbers (1-7, minus 6). In addition, I would like to select any one, or multiple Shavers, so the data populated under the Shaver headers only return data based on what is selected, and those Shaver's not select remain blank (see below examples). The second example does not have any correct formulas, I just copied/pasted based on what I am looking to create.

If anyone has some suggestions, or ideally some YouTube videos that showcase more than just the basic uses for Radio/Checkmark buttons, I would be greatful.

Formula in cell C5: =INDEX('Pivot Table'!$B$12:$C$17,ROWS('Pivot Table'!$A$12:A12),$A$1)

1710954547176.png


1710954732886.png
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
This can be done to some extent without macros.
All you have to do is link those checkboxes to a cell. Then the cell will have a value TRUE or FALSE depending on the state of the checkbox.
If you change the text color in these linked cells to WHITE or set their Custom Number format to SPACE character - then the values TRUE/FALSE will not be visible to spoil the effect. Or you can have all linked cells in a hidden column.
e.g. Checkbox "Shaver 2" is linked to cell A5, Shaver 3 to A6, All Shavers to A4 etc.
Then the formula for each shaver amount should check if all shavers are TRUE, if not check if the cell for the particular shaver is TRUE - if one of those is TRUE then get the amount for this shaver.
e.g. for Shaver 2 something like:
Excel Formula:
=IF($A$4,{your formula for the Shaver 2},IF($A$5,{your formula for the Shaver 2},""))
The only drawback of not using code is that changing the state of 1 checkbox will have no effect on the state of the rest of them e.g. checking "All Shavers" cannot make all others checked as well.
But maybe this is not so important.
 
Upvote 1
That's what I was kind of toying around with, but was hoping for something more 'compact', so to speak. I haven't studied VBA/Macros yet, but sounds like I'll need to jump on those before I can make a truly interactive dashboard.

Thanks bobsan42.
 
Upvote 0

Forum statistics

Threads
1,223,181
Messages
6,170,582
Members
452,339
Latest member
mrlong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top