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.

Andrée Starå

Hi @laura.buchanan

I hope you're well and safe!

To add to Lucas's excellent advice/answer.

Here's another option. I prefer to use <>"" to check if something is blank, and I also added an IFERROR part, so you don't get an error message when nothing is filled in.

=IF(ColumnA@row <> "", ColumnA@row, IFERROR(INDEX(COLLECT(ColumnA:ColumnA, ColumnA:ColumnA, <>"", Email:Email, Email@row), 1), ""))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Lucas Rayala

@Andrée Starå, I've basically swapped over to the LEN function to determine null vs non-null -- it works the same for Excel, Smartsheets, Python, or SQL and covers different data types. I was using an array of ISBLANK, ISDATE, <>"", etc. but now if LEN works, I just use it as default. But point made, <>"" is intuitive.

Andrée Starå

@Lucas Rayala Yes, I used to use NOT ISBLANK, but I prefer <>"" or ="" because it's shorter.😉

laura.buchanan

Gentlemen, Thanks so much for the suggestions! I changed Hire Date to Column A and added a Column B so it would be easier to just copy and paste. It is fine for me to use two columns, simpler that way.

Both of the suggestions work great!

I am SO HAPPY! Smartsheet is SO MUCH FUN!

Can one of you dissect these formulas for me to learn for future. I have only done simple sumif, if and countif formulas. These are blowing me away.

Also could you explain the IFERROR and the INDEX items a little bit. Seriously pretend I'm your grandmother, because formulas are hard for me.

One more thing - is there a specific class you'd recommend I take regarding formulas?

Thanks bunches!!

L

=IF(ColumnA@row <> "", ColumnA@row, IFERROR(INDEX(COLLECT(ColumnA:ColumnA, ColumnA:ColumnA, <>"", Email:Email, Email@row), 1), ""))

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

Andrée Starå

@laura.buchanan

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!