Google user
Original Poster
Sep 27, 2019
Mutually Exclusive Tick box options (Radio Button?)
I have four Tick Boxes in a column A1:A4. I would like to make it so only ONE tick box can be ticked at any one time. For example, if A1=true then A2:A4 are false - however, if I then tick A3, all the other should be false. Hopefully that makes sense.
I'm a complete amateur when it comes to Sheets (or Excel)... I'd never really done anything beyond simple addition equations until today when I had bash at IF and OR functions as well as using mixed text and number functions... so go easy on me ;) Might have to explain your answers like you're speaking to a toddler :)
Thanks all!
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Sep 27, 2019
needs a script
here an example : sauce
Spencer Farrissupport.google.com/docs/thread/13726711
for your sheet
try this script
function onEdit(e){
if(e.source.getActiveSheet().getName() != "Sheet Name" || e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i = 1;i<5;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}
if(e.source.getActiveSheet().getName() != "Sheet Name" || e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i = 1;i<5;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}
if you dont know how to add a script
menu - tools script editor
paste above in place of whatever is there
change sheet name to your sheet name
done
Please ignore - forum violation - over-ride text
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Original Poster Google user marked this as an answer
The script was written explicitly for a user who had the boxes in B, but you have them in A. So in the initial IF() statement it is ensuring the edit was made in A (e.range.columnStart != 1) but then the setValue() section of the FOR() statement is outputting the values to B.
Change the line to
e.source.getActiveSheet().getRange(i,1).setValue("FALSE"); and it should work.Original Poster Google user marked this as an answer
All Replies (6)
needs a script
here an example : sauce
Spencer Farrissupport.google.com/docs/thread/13726711
for your sheet
try this script
function onEdit(e){
if(e.source.getActiveSheet().getName() != "Sheet Name" || e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i = 1;i<5;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}
if(e.source.getActiveSheet().getName() != "Sheet Name" || e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i = 1;i<5;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}
if you dont know how to add a script
menu - tools script editor
paste above in place of whatever is there
change sheet name to your sheet name
done
Please ignore - forum violation - over-ride text
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Original Poster Google user marked this as an answer
Sep 29, 2019
This is my first time with any scripting beyond flowchart/chaning a few .ini files in games..
Again, thank you so much already
Hey Luke,
This is a special type of script designed to run automatically whenever an edit is made. The E variable is passed by the edit event, and does not exist without an edit. As such, you cannot run it manually. Make sure your sheet names and other variables and are correct and then edit something (clicks checkbox) and it should run.
Hope tgat helps!
Google user
Original Poster
Oct 1, 2019
I've got the script to do something but I'm not sure why its acting the way it is.
The script is as follows:
The script is as follows:
function onEdit(e){
if(e.source.getActiveSheet().getName() != "Sheet1" || e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i = 1;i<5;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}
Which I believe is exactly as you described with the sheet name changed correctly.
Which I believe is exactly as you described with the sheet name changed correctly.
however, instead of acting upon the tick boxes in A1:A4, it has created a "false" value in B1:B4 which replaces itself once deleted and doesn't seem to interact with much else.
Here's my sheet: https://docs.google.com/spreadsheets/d/11G3WpVVYjdIlNL3pWLswrS5b7wlXmTabDiUaFcV_nQ8/edit?usp=sharing
No idea what I'm doing wrong!
Thanks :)
Here's my sheet: https://docs.google.com/spreadsheets/d/11G3WpVVYjdIlNL3pWLswrS5b7wlXmTabDiUaFcV_nQ8/edit?usp=sharing
No idea what I'm doing wrong!
Thanks :)
The script was written explicitly for a user who had the boxes in B, but you have them in A. So in the initial IF() statement it is ensuring the edit was made in A (e.range.columnStart != 1) but then the setValue() section of the FOR() statement is outputting the values to B.
Change the line to
e.source.getActiveSheet().getRange(i,1).setValue("FALSE"); and it should work.Original Poster Google user marked this as an answer
Google user
Original Poster
Oct 2, 2019
Thank you SO much for you help with this!
Works exactly as intended now and I cannot thank you enough :)