Display date from different row in same sheet

laura.buchanan

I have a Smartsheet with employee names, email and hire date.

Approximately every year employees are required to upload an updated resume, using a form to populate the sheet.

On that new row created by the form, the hire date is blank because employees can't be counted on to remember the correct date. We want to use the hire date entered previously by administrative staff when they were hired.

So something like this, if hire date is blank and the Email@row is somewhere else on the sheet, display Anniversary date.

Can someone assist? I'm thinking this might be an IF/Then formula but not sure.

Thanks! LB

Lucas Rayala

Hi @laura.buchanan, the Hire Date column can't be both a data input field and a formula. You can have two columns, where Column A is the Hire Date as entered by the HR staff in the original line entry, and Column B is a formula that collects the appropriate hire date from that original line entry for all matching emails. The formula in Column B would be:

=IF(LEN(ColumnA@row)>0, ColumnA@row, INDEX(COLLECT(ColumnA:ColumnA, ColumnA:ColumnA, LEN(@cell)>0, Email:Email, Email@row),1))

If you want all the dates to end up in the original Hire Date column and you have DataMesh, you can do that by making both the source and target sheets the same sheet you are working on, use your email column as your unique ID, and copy from Column B to Column A. Test it on a copy of your sheet first so you don't blow everything up.