Using Isblank across sheets and for one column
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?
Best Answer

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
Answers

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

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

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)

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.

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.

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?

You would use an IF statement.
=IF([Checkbox Column name]@row = 1, original_formula)

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
Help Article Resources
Categories
Check out the Formula Handbook template!