Using Isblank across sheets and for one column

daonnen

I am attempting to have a check box populate if there is a duplicate between two sheets.

I currently have it checking both sheets with this formula

=IF(OR(COUNTIFS({All Dept. Contacts Range 1}, [Last Name]@row) > 0, COUNTIFS([Last Name]:[Last Name], [Last Name]@row) > 0), 0, 1)

But it just checks the box even if it is blank or there is not a duplicate in the other sheet.

How do I get it to only check the box if there is for sure a duplicate in the other sheet?

daonnen

I Used this formula

=IF(Approved@row = 0, IF(COUNTIFS({All Dept. Contacts Range 3}, [Last Name]@row, {All Dept. Contacts Range 1}, [First Name]@row) + COUNTIFS([Last Name]:[Last Name], [Last Name]@row, [First Name]:[First Name], [First Name]@row) > 1, 1))

and it worked perfectly for me

Paul Newcome

Try adjusting it so that the second COUNTIFS is greater than 1 instead of greater than 0. You will ALWAYS have greater than zero on the same sheet since there is at least one row containing that particular last name (otherwise you wouldn't have anything to compare to).

daonnen

I tried that and it just gave me the #Incorrect Argument error, same as before

Paul Newcome

This gives you an Incorrect Argument error?

=IF(OR(COUNTIFS({All Dept. Contacts Range 1}, [Last Name]@row) > 0, COUNTIFS([Last Name]:[Last Name], [Last Name]@row) > 1), 0, 1)

daonnen

That one did not give me an error but it will not check the box if I have a duplicate in the other sheet, it still leaves it blank.

Paul Newcome

Ok. I honestly haven't used that particular logic set before to check for doubles, so maybe I am misreading it. Here is what I usually use:

=IF(COUNTIFS({All Dept. Contacts Range 1}, [Last Name]@row) + COUNTIFS([Last Name]:[Last Name], [Last Name]@row) > 1, 1)

I usually add the two COUNTIFS together and say that if the total is greater than 1 then check the box.

Looking closer at yours, it seems like you need to switch around the 1 and the 0 at the end. Right now you are saying to leave the box unchecked if either are true and to check the box if both are false.

daonnen

So this worked however now I have another problem. I have an approved check box as well and only want to formula to run if that box is unchecked. How can I adjust the formula to do this?

Paul Newcome

You would use an IF statement.

=IF([Checkbox Column name]@row = 1, original_formula)

daonnen

I Used this formula

=IF(Approved@row = 0, IF(COUNTIFS({All Dept. Contacts Range 3}, [Last Name]@row, {All Dept. Contacts Range 1}, [First Name]@row) + COUNTIFS([Last Name]:[Last Name], [Last Name]@row, [First Name]:[First Name], [First Name]@row) > 1, 1))

and it worked perfectly for me