Override the data in the cell when checkbox is clicked

Hi
I need some help on overidding the data via checkbox.

Basically i am working on a spreadsheet that is doing two things 1) its allowing the user to choose between 1 to 3 number or N/A from the list validation option and 2) there is a checkbox if one selects that all the columns that have the formula will become N/A irrespect of data in it.

I am using the following formula in the list validation option
a3 is the check boc
E5 =1
E6= 2
E7 = 3
E8=N/A

=if(a3=true, $E$8, $E$5:$E$8)

The issue I am having with this is, if I start selecting something from the drop down and half way I realize I need to check the box, it will only make it E8 value for the cells I have not touched and the ones I have already selected the drop down value it will remain. However I want it to override the value to E8 irrespect.

1) If the user of the spreadsheet clicks on the check box (this is in A3 cell) which i have linked it using format control then all the cell (Column E) that have the formula =if(A3=True, "N/A", ) will have N/A which is not applicable and not an error as you mentioned. I have successfully implemented this

2) If the user does not select the check box (a3) then each of the cell (Column E) has a drop down which the user can select either 1,2,3 or N/A. Which I have successfully implemented by using the data validations

Now the struggle I am having is as follows: I have attached the spreadsheet for your reference too.

Now if I select number 2 in cell E10, 2 in cell E11 and 3 in cell E 13 and you can see alll the other cell in column E are 0, because of the formula and the box in A3 is not checked in Tab 1. In Tab 2 I have checked the box everything turned N/A in column E except cells E10, E11 and E13 because I had previously selected 2,2,3 respectively.
This is not what I want, what I want is when I check that box everything should turn to N/A basically an override function is what I want to created.

Right-click your little checkbox and select Assign Macro.
Select New.
Paste in this code:

Please Login or Register  to view this content.

Press Alt-Q to close the VBEditor.

Now click your checkbox.

Thank you for all the help, it worked, you are awesome

Glad to hear it.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)