Formula stopped working

Pamela Ivey

I have a formula that was working and all of a sudden stopped working but no changes were made other than new rows added. The formula looks for duplicates in either the employee name or employee ID:

=IF(OR(COUNTIF([Employee Name]:[Employee Name], [Employee Name]@row) > 1, COUNTIF([Emp ID for duplicates]:[Emp ID for duplicates], [Emp ID for duplicates]@row) > 1), 1).

It works perfectly in our test environment but stopped working in our live production environment and now gives a #INVALID VALUE result. I've tried deleting the column, recreating it and copy/paste the formula from our test environment that is working and I still get the same invalid value message.

Any help or guidance would be greatly appreciated.

Thanks!

Genevieve P.

Hi @Pamela Ivey

Is it possible that one of the referenced columns has a formula error in it?

How are the [Employee Name] and [Emp ID for duplicates] columns being populated, and do either of these columns have a formula error message? This can create a domino affect where your second formula returns an error if it encounters one in a different column.

Mark Cronk

Hi @Pamela Ivey ,

Confirm that the column types are correct. Names and IDs can be text/number or single drop down. The colum with the formula can be text/number, checkbox or symbol. Confirm your column names match your formula. The ranges in your formula should be colored when you click in the formula. If not, you have a text error someplace.

If that doesn't solve your problem, then try:

=IF(OR(COUNTIF([Employee Name]:[Employee Name], @cell=[Employee Name]@row) > 1, COUNTIF([Emp ID for duplicates]:[Emp ID for duplicates], @cell=[Emp ID for duplicates]@row) > 1), 1, 0)

Work?

Mark

Pamela Ivey

Hi Mark, thank you for your response. My original formula was working and suddenly stopped, there was no change to column properties or the sheet, other than new rows were added. All columns in the formula are text/number. The same exact formula works on a test copy of the sheet with the same definitions. Your suggested formula did not work in the problem sheet, I get the same error message. It did work in the copy, just as the original formula.

Genevieve P.

Hi @Pamela Ivey

Is it possible that one of the referenced columns has a formula error in it?

How are the [Employee Name] and [Emp ID for duplicates] columns being populated, and do either of these columns have a formula error message? This can create a domino affect where your second formula returns an error if it encounters one in a different column.

Pamela Ivey

Hi Genevieve, thank you for your response. Emp ID for duplicates does have a formula: =VALUE([Employee ID]@row) and Employee ID is populated via a form. I went through the data in that column and one of the rows does have an invalid entry, the Employee ID has an alphanumeric value rather than numeric. Updated to the proper value and formula is back to working correctly for the sheet!

Thank you so much for your help!

Genevieve P.

Wonderful! I'm glad we could figure out the root cause 🙂