Move Rows Not Working

SEBERCAW

I have a form tied to a sheet that tracks Name, Work Email, Hire Date and dates for various Safety Credentials. In this Source sheet, I have a formula to track if the person's Name entered into the form is unique. If it is unique, I have workflow that moves the entire row to a Destination sheet.

Everything works, except for columns on the Destination sheet with this formula:

=IFERROR(INDEX(COLLECT({HIRE DATE}, {NAME}, NAME@row, {HIRE DATE}, <>""), 1), "")

(Obviously, the HIRE DATE gets replaced with the appropriate name of a column).

Nothing appears in the Destination sheet for any column with this formula. The name and other columns are moved but the columns with this formula do not.

DESTINATION SHEET.JPG

FORM.JPG

SOURCE SHEET.JPG

Paul Newcome

What happens when you remove the IFERROR portion?

SEBERCAW

It then puts the value #Invalid Value

Paul Newcome

It sounds like you aren't getting a match then. I notice in your screenshots that Austen doesn't have a match for hire date.

SEBERCAW

The person that I filled out with the form, i erased because it is not working.

SEBERCAW

I have tested it and erased the data, so it would be moved each time.

Paul Newcome

Are you able to provide a screenshot of a matching row that is throwing the error/not showing data?

SEBERCAW

Below are two screenshots.

The first is with the user, Susan Bercaw, filling out all entries on the form and the IFERROR part of the formula intact.

The second is the same but with the IFERROR part of the formula taken out of the HIRE DATE & EEO/HARRASSMENT columns.

Thanks for your help

DESTINATION WITH IFERROR.JPG

DESTINATION WITH IFERROR NOT ON HIRE DATE AND EEO-HARASSMENT.JPG

SEBERCAW

Also, here is a document I wrote to explain the steps I took.

PROVIDENCE SMARTSHEET SAFETY CREDENTIALS SETUP GUIDE.docx

Paul Newcome

Lets try using @cell references...

=IFERROR(INDEX(COLLECT({HIRE DATE}, {NAME}, @cell = NAME@row, {HIRE DATE}, @cell <>""), 1), "")

SEBERCAW

The #INVALID VALUE, disappears and the cell is blank.

SEBERCAW

I want to thank you for your time, Paul Newcome.

The answer to my problem was to copy the row in the automation and not move it. Thanks again for all the time you took to help me.

Paul Newcome

Glad you got it working.