Excel or Google sheets question

[nothanks]

I'm struggling with a bit of logic in google sheets - do we have any experts here? I want a cell to contain a ticked yellow tickbox if a tickbox in another cell is ticked and, if the first isn't ticked then the second should contain an unticked (but tickable) tickbox. Suggestions?

[newshound]

Sounds as though it should be straightforward with Excel conditional functions, but I don't know about Google Sheets.

[Robin]

Depends on the /precise/ options you want. E.g. in Excel if you code the first

"Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then CheckBox2.Value = True If CheckBox1.Value = False Then CheckBox2.Value = False

End Sub"

then 2 is automatically ticked when 1 is ticked BUT is also automatically unticked when 1 is unticked even if 2 has been manually ticked while 1 was unticked. I think.

But you'd do better to ask in one of the Excel forums. I've occasionally used

without losing life or limb.

[John Rumm]

So for example I have cells B1 and B2 formatted as checkboxes. The formula in B1 is "=B2"

Then I have conditional formatting applied to B1 with format rules of "Is equal to", and a value of "TRUE", then style then setting the text colour to yellow.

Now if I tick B2, B1 then also ticks, and changes to yellow.

Is this what you are after, or do you also want to be able to manually tick B1 yourself?

[nothanks]

Yes, I need to be able to manually tick B1 as well - that's the tricky bit.

[John Rumm]

and if you tick B1, what happens elsewhere?

Do you in effect want manually ticking B1 to act as an override - such that if you tick it it stays ticked - but does not go yellow? If you untick it, then it is slaved to the content of B2?

ISTM you have created a circular dependency, and would be better with a third cell acting as a tellback for B2?

[nothanks]

I just haven't explained it well. Let me have another go at explaining what I need: A1 and A2 contain tickboxes, either can be manually "ticked" If A2 is ticked I want A1 to both change to "ticked" and change colour If A2 is unticked then A1 reverts to unticked and base colour If A1 is ticked it does not change colour and A2 is not affected.

[Robin]

May I check {sic) you want A1 to revert to unticked even if A1 was manually ticked before A2 was ticked?

[nothanks]

Hmm, a good question ... it would be preferable, but not essential, for it to revert to its previous state.

[Robin]

OK

Sorry, I should have asked sooner: do you need a sheet that works in Google Sheets, or in both Google Sheets and Excel? Or indeed wd just Excel do?

[nothanks]

It would be preferable if it worked in Google Sheets, but if that's not possible then an Excel solution would be OK. Over the decades I've built some ferocious spreadsheets but have never had to use macros, which I presume is the way to get the function I want.

[John Rumm]

Well here is one fairly fugly (and not particularly quick) solution...

Setup tick boxes in a A1, A2, and A3 - A3 is just a cache of what was manually selected in A1 - it doe snot need to be visible or even on the same worksheet, but it gets round the problem that any memory of what happened previously is lost when the script exits.

I have created an "onChange" even and added it to the sheet to call a function called onChange *after* every change to the sheet.

Once we are in that function we have to infer what happened recently by what the current cell is. Unless it was either A1 or A2 we ignore it

So you click, and onChange runs:

/** * This event handler has been linked to the "onChange" fired after a * change is made to any of the worksheets * in the spreadsheet. */

function onChange( e ){

// Event object will contain the spreadsheet object that generated the event const sheet = e.source.getActiveSheet()

let firstTickbox = sheet.getRange( "A1" ).getValue() let secondTickbox = sheet.getRange( "A2" ).getValue() let cachedTick = sheet.getRange( "A3" ).getValue()

let activeCell = sheet.getCurrentCell()

Logger.log( "in event cell " + activeCell.getRow() + "," + activeCell.getColumn() )

if( activeCell.getColumn() == 1 && activeCell.getRow() == 1 ) {

Logger.log( "in A1 which was just " + (firstTickbox ? "ticked" : "unticked") )

if( firstTickbox ) { // A1 just ticked activeCell.setBackground("white") } else { if( secondTickbox ) sheet.getRange( "A1" ).setBackground("yellow") else sheet.getRange( "A1" ).setBackground("white") }

sheet.getRange( "A3" ).setValue( firstTickbox )

} else {

if( activeCell.getColumn() == 1 && activeCell.getRow() == 2 ) {

Logger.log( "in A2 which was just " + (secondTickbox ? "ticked" : "unticked") )

if( ! firstTickbox ) { // first box is not already ticked, so we are free to change it Logger.log( "not first tick") if( secondTickbox ) { Logger.log( "second tick") sheet.getRange( "A1" ).setValue( true ) sheet.getRange( "A1" ).setBackground("yellow") } }

if( ! cachedTick ) { Logger.log( "not cached tick") if( ! secondTickbox ) { // Value of first box does not match cached one, so it must have been a change to A2 that caused the tick sheet.getRange( "A1" ).setValue( false ) sheet.getRange( "A1" ).setBackground("white") } } } } // not interested in any other cells }

[Robin]

I should have known that John Rumm would rattle off a solution while I was still in me pit and checked before putting some /exceedingly/ clunky VBA code in a sheet this morning. But having done it I might as well let you have it.

It remembers if A1 has been manually clicked so I added a further rule to avoid ambiguity: "If A2 is already ticked then clicking A1 has no effect"

NB you need to open this using "desktop" Excel

Sadly VBA does not translate into Google Sheets

[John Rumm]

The GAS code was a bit clunky as well - it might be neater using an "onEdit" event handler to put the edit action under script control rather than letting the spreadsheet process the click on the tick boxes itself and make its own decisions.

[Robin]

I might try that next week with your code as a starter: bit of a challenge for me as I never got around to JavaScript.

thanks - I'll have a look at that next week too

[John Rumm]

Not sure I would start with that particular bit of code :-)

But GAS itself is quite versatile and powerful and well worth having a play with. Very good at building "mash up" applications; e.g. have a sheet dynamically update a form that submits user data back to a sheet, that reaches out to an external rest API, updates a JDBC database before emailing a PDF of a auto generated Word doc etc.

Have a look at the youtube channel "Learn Google Spreadsheets" - that has a very heavy focus on scripting and the chap who does them is very good at it.

I found JS itself quite difficult to get into - partly because my history is with strongly typed compiled languages. JS at first just seems very "woolly" with its dynamic typing and slightly unusual object model. However with time I have got more comfortable with it. (although worth noting I uses it predominately server side, and have little interest in using it in a browser)

To be fair I have not tried it, although knew of its existence. Never had much VBA that needed converting.

[nothanks]

I'm very grateful to both you and John, but my lack of knowledge means I can't immediately see how to apply these solutions to the full problem so I need to decide whether to simplify the problem or invest a lot of time ... because the job list has infinity+1 entries at the moment simplification is most to be the answer for the time being.

[Robin]

No worries. I've learnt useful stuff about Google Sheets along the way.

I did wonder if the combination of tick boxes and colours is compressing too much into 2 cells and causing more trouble than it's worth. Happy to look at the underlying issue if you wish.

[John Rumm]

Here is the "how to get started from knowing nothing" guide:

In you sheet, look at the "Extensions" menu. You should see "Apps Script". Choose that and it will open a code editor. In it will be a placeholder function called "myFunction". This serves no purpose other than hinting where you will put your code! You can delete it or rename it if you want.

Now there are several ways you can use a script. The easiest is you add a custom function to the spreadsheet. So say you wanted a function that could double a number, you could write:

function Double( value ){ return 2 * value }

Save that (CTRL + S)

Now in your sheet, you can enter the name of your function like any other function you might use with a formula like "=Double( 5 )" and it will show 10 in the cell.

(Note the first time you run a script you will be prompted to give it permission - you need to click through the various prompts about trusting the developer and allowing it to run in your account etc).

The next way is you have a function that you only run from the scripting environment, but that does something useful with your sheet. So say for example you have just built a sheet that is snazzy and does what you need, but you really need a copy of it for every month of the year. Now you could manually select "Duplicate", and then "Rename" and repeat a dozen times, or you could run a script like;

function makeMonths(){ const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec" ] var ss = SpreadsheetApp.getActiveSpreadsheet() var page = ss.getSheetByName("Template") for( let month of months ) { var newpage = page.copyTo( ss ) newpage.setName( month ) newpage.showSheet() // Just in case Template is normally hidden. } }

The next step on from that is it to link it to the UI of your sheet in some way. A common way would be to add a custom menu to call your functions from the sheet rather than needing to go to the development environment. If you add code like:

function onOpen(){ SpreadsheetApp.getUi().createMenu( 'Sheet Utils') .addItem( 'Copy template to months', 'makeMonths' ) .addItem( 'Delete months', 'deleteMonths' )

.addToUi() }

Where the deleteMonths code might look like:

function deleteMonths (){ var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() for( var i = 0 ; i < sheets.length ; i++ ){ var sheetName = sheets[ i ].getSheetName() if( sheetName != "Template" ) // don't delete template SpreadsheetApp.getActiveSpreadsheet().deleteSheet( sheets[ i ]) } }

When that is run it will add a new menu to the sheet to the right of the help menu. It will be called Sheet Utils, and have two options.

Note that the name for the function "onOpen" is a special one that will run automatically when you open the sheet. So after brief delay at startup you will see the menu appear

[John Rumm]

As a general rule, trying to use one cell as both an input and an output is always difficult in spreadsheets since if you put a function in it, it will get overwritten when a user inputs something into it. Using extra cells for tellbacks is preferable.