• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check Box Concept [SOLVED]

Manster

Manster

Member
Hii all,


First of all I wish to thank who are now reading my post for this time. Actually, I need to understand the concept to do this task in excel as per below:


Situation: I have 5 Check Boxes. (Developer>Insert>Form Control)

A1 column is a main, which means the result will show here (A1).


Task: When I 'tick' the 1st Check Box, the result will show 'A' in A1 column.

Then, I 'tick' the 2nd Check Box and the result in A1 change to 'B'. Followed by 3rd become C, 4th become D and 5th become E. But the result just in A1 column only.


Remark: The result can be changed to 'B' for example without 'untick' the 1st check box. Means that both of the check boxes are 'ticked' but A1 column can be changed from 'A' to 'B'.


Hopefully, all my friends and especially all master's in chandoo forum can help me.


Thank you.
 
Are you looking for the box that was checked most recently, or the highest number checkbox? What do you want to happen when you uncheck a box?


https://docs.google.com/file/d/0B0di3bZpuEb0VXlJMDZMVEhiSVk/edit?usp=sharing


This will show the A-E in cell A1. I'm having the checkboxes link to cells D1-D5, and then multiplying the TRUE/FALSE responses by 1-5 in Cells E1-E5. Then I have A1 look for the maximum value in E1-E5 and convert it to the letter using CHAR(64+Max(E1:E5)), with an if statement to have A1 blank if no checkboxes are selected. There are going to be several other ways to do this. VBA would give you a more robust setup.
 
Manster


You may also want to explain what your trying to achieve with the result of A1


As there may be other approaches to setting or processing the value instead of needing 5 check boxes


As always posting a sample file is preferable

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi ,


A simple IF statement should do the job :


=IF(D14,"E",IF(D11,"D",IF(D8,"C",IF(D5,"B",IF(D2,"A","")))))


Link the first check box to cell D2 , the second to D5 , and the others to D8 , D11 and D14.


Narayan
 
Here below my table that need solution from Chandoo's members.


https://www.dropbox.com/s/xfb47rkvjwhgqb1/Book1.xlsx


Current flow

When we tick P1 on the check box, it will show 45. Followed by P2 will show 6, P3 shows 21 and last P4 shows 20.


Problem:

What is the formula if I want to click P2 and it didn't show the result as 6, then, when I click on P3 it will show 21 in the result?


Thanks in advance.


Regards.
 
Hi ,


I don't know whether I have understood your problem.


In B8 , put in the formula :


=IF(B9,B10,"")


Copy this to C8 , D8 and E8.


In F8 , put in the formula :


=IF(E9,E10,IF(D9,D10,IF(C9,C10,IF(B9,B10,""))))


Narayan
 
Manster


If P1 and P3 are ticked with P2 & P4 blank what should it show?


Should F8 sum the value that are ticked?

eg: =SUMPRODUCT(B10:E10*B9:E9)
 
Thanks Narayan replied my post. Your formula is approaching to the objective now.


However, the objective is when we CLICK the P2 checkbox, normally it will show C8 value, but the objective is we don't want F8 showing the value in C8 without we untick the P2 box.


Then, we continue as normal to P3 and it will show the result in D8 and F8 like we have done before in P1. Hopefully Chandoo's Masters especially Narayan will help me...


Really appreciated.
 
Dear Hui,


It doesn't mean we count/sum the values. Just shows the values B8 in F8 when we tick P1 (45), then we tick P2, suppose it shouldn't show the value in F8 (value is 6), then when we tick P3, it shows the value like we done in P1. After that, we tick P4 and shouldn't shows the value in F8. Its like 'flip-flop'. Value, no value, value, no value, without we untick the box.


Thanks Hui responded my post.
 
Hi ,


If I have understood you , P1 and P2 checkboxes switch ON / OFF the display of F8 ; the same is true of P3 and P4.


Normally such mutually exclusive operation is through grouped radio buttons , so that when one radio button in a group is selected , the others in the same group are automatically de-selected , without the need for any formulae.


Try this in F8 :


=IF(AND(D9,NOT(E9)),D10,IF(AND(B9,NOT(C9)),B10,""))


Narayan
 
Wow, amazing+brilliant!! Thank you very much Master Narayan and Master Hui. Both of you trully Excel Ninja!!
 
You must log in or register to reply here.
Back
Top