Formula to clear worksheet option buttons without vba

paulma1960

New Member
Joined
Aug 23, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I require a non-VBA solution for a problem, and I'm wondering if there's a viable solution. I'm using option buttons for an employee skills survey. Each employee can self-assess their skill level in various areas of technologies. By default, I want the sheet to open with all option buttons cleared. The linked cells are in column A, and of course, I achieve this by deleting the values in column A. There are quite a few sections, and I would like to create a shortcut for the user, where they have no skills in a particular field, to be able to click a check box that would set all the option buttons to zero.

The checkbox at Cell D6 is linked to the cell at Cell K6. By default, it is unchecked, so the value at K6 would be either blank or FALSE. I was thinking of using a formula in column A, that links to the checkbox selection. The formula in cell A7 (next to MS Access) would look like:
Excel Formula:
=IF(K$6=TRUE, 1, 0)
.
This works fine, but once only, because once an option button has been selected, it will overwrite the formula in column A with the selected index. I'm wondering if there is some other formula trick I could use. Any suggestions appreciated, and thanks in advance. Paul

1629750963454.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
There is no such thing as a formula solution for what you are asking, it can only be done with vba.
 

paulma1960

New Member
Joined
Aug 23, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
There is no such thing as a formula solution for what you are asking, it can only be done with vba.
Yes, I understand. I thought I'd give it a try, as I never cease to be amazed at the creativity of solutions I sometimes come across. Thanks anyway, Jason.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
I never cease to be amazed at the creativity of solutions I sometimes come across
It is often the case that when you think that something can not be done, there is somebody who will prove you wrong. Unfortunately that is not possible for what you want to do.

Something that did come to mind (I've never tried it for anything like this) is to try using solver to see if it is possible to set the link cells as required by setting a reference formula in another cell.
I'm not sure that it will work but could be worth a try?
 

paulma1960

New Member
Joined
Aug 23, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm more familiar with VBA, so I'm going to go that route, thanks Jason.
 

Forum statistics

Threads
1,148,194
Messages
5,745,276
Members
423,942
Latest member
excelhelp1423

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
Top