Google Sheets Formulae

Ask for Help
1

Evening guys, I wanted to post this in the Spreadsheet Magic section but I don’t seem to have permission so hopefully it is ok to post it here instead.

I have 2 Google Sheets spreadsheets that I have been working on (one for the purpose of my first app and the other is a more long term, in depth project which I hope to transfer to an app once I have learnt more about the processes). However, in both I have a couple of sticking points of which I would like to see if anyone can assist with.

  1. The easier one relates to a formula to automatically tick a checkbox when certain values match. Details as below:

When the value in A1 of Sheet 1 matches the value in A1 of Sheet 2, I would want a checkbox in B1 of Sheet 1 to be ticked. I have tried a number of IF formulae and just don’t seem able to get it to work.

  1. The more complex one is a formula that I have been working on for a few weeks now and although I have gotten a lot closer, I am still coming up short. This one relates to an importrange formula to be used in conditional formatting. The formula I have used most recently is:

=MATCH(AE15, FILTER(IMPORTRANGE(“1elDxSOLSG-6U-jC6E5fgcKeJDNf9Kcu7GRGvK5p6fr4”, “Haulage!J3:J”), IMPORTRANGE(“1elDxSOLSG-6U-jC6E5fgcKeJDNf9Kcu7GRGvK5p6fr4”, “Haulage!J3:J”)<>“”))

What I want this to do is to check values in the range J3:J of ‘Haulage’ worksheet within the spreadsheet that the key refers to and should any of these values match that of the value of cell AE15 in my main spreadsheet, I would want cell AE15 to be highlighted.

The results of this formula seem to work for any data already in the range J3:J but doesn’t have any impact when I edit data in this range ie if I delete a value from a cell within the range, the cell it corresponds to in the main spreadsheet remains highlighted when it should lose it’s formatting.

I hope this makes sense and I hope someone can assist as I feel like I am banging my head against a brick wall with them :smile:

2

Hi mattja19,

As the first question looks easier to solve, I will give the answer first.

It can be done by:

=IF(‘Sheet 1’!A1=‘Sheet 2’!A1,‘Sheet 2’!B1 = TRUE, FALSE)

If the values in A1 of Sheet 1 and A1 of Sheet 2 are equal, the value in B1 of Sheet 2 will be set to TRUE, which corresponds to a tick in your case. Otherwise it’s not ticked, as in FALSE.

1 Like
3

For the second case, here’s my proposal:

1st step: Have a “helper” cell with the formula, let’s say you put it in cell B1.

=CONCATENATE(J3:J)

This will concatenate all values you have in the J3:J range into a single cell.

2nd step: Set the conditional formatting in cell AE15 to:

=FIND(AE15, B1) > 0

This will get the value in AE15, search for it in the concatenated string in B1, if it matches it will return a value larger than 0, hence set the conditional formatting as you wish.

Hope it helps. Please get back to me if it does not work by commenting here or send an email over at ariesarsenal@gmail.com.

4

Hi ThinhDinh,

Thanks very much for your responses.
I have been able to play around with your first answer to be able to make it work, so thanks a lot for that. Unfortunately time has been against me recently so not had the time to see if I can make things work with regards to your 2nd answer - I will, of course, offer you feedback once I get around to it :slight_smile:

1 Like
5

No worries mattja19, just give me a reply if you need further assistance.