Formula to clear worksheet option buttons without vba

[paulma1960]

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:

=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

 

[jasonb75]

There is no such thing as a formula solution for what you are asking, it can only be done with vba.

 

[paulma1960]

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]

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]

I'm more familiar with VBA, so I'm going to go that route, thanks Jason.