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.
- 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.
- 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