Function to check box if lookup is true

Anthony556565

Hi

Is it a possibility where a column contains checkboxes, for a function to sit behind that that if a lookup to another sheet returns a true value, the checkbox is ticked?

Specifically for the lookup to be:

On Sheet1: If the value in col ID and the date in col Next Inspection Date appear in sheet2 (below) in the cols 'ID_user' and also col 'until' --> that the checkbox on sheet1 is ticked.

Sheet1

Sheet2

Thanks for any assistance

Paul Newcome

You need to make sure you are creating your cross sheet references correctly.

Type this...

=IF(COUNTIFS(

Then you should see a little blue link in the formula helper box that says something along the lines of "Reference another sheet". Click on that, and it will open another dialog window. Select the sheet you want to reference, then click on the appropriate column header. Once you click on "Insert Reference" in the bottom right corner of the dialog box, it will close that, go back tot he sheet you are working in, and you should see the reference now inserted into the formula right where you left off.

Paul Newcome

Try this...

=IF(COUNTIFS({Sheet 2 ID_user Column}, @cell = ID@row, {Sheet 2 until Column}, @cell = [Next Inspection Date]@row) > 0, 1)

Basically we count how many rows in sheet 2 have the same ID and Date. If that count is greater than zero (meaning there is at least one match) then we check the box.

Anthony556565

Hi Paul

Really appreciate your assisting. I have entered something incorrectly as I receive an invalid ref, if it may stick out what I have done.

Also could I ask is there something I do in the column properties also so that when a value is returned that triggers the check box to tick?

Paul Newcome

You need to make sure you are creating your cross sheet references correctly.

Type this...

=IF(COUNTIFS(

Then you should see a little blue link in the formula helper box that says something along the lines of "Reference another sheet". Click on that, and it will open another dialog window. Select the sheet you want to reference, then click on the appropriate column header. Once you click on "Insert Reference" in the bottom right corner of the dialog box, it will close that, go back tot he sheet you are working in, and you should see the reference now inserted into the formula right where you left off.

Anthony556565

Hi Paul

That's fantastic and all worked. Very appreciative of your help.

Thank you

Paul Newcome

Happy to help. 👍️