Display date from different row in same sheet

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

Answers

  • Lucas Rayala
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Lucas Rayala
    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å
    Andrée Starå ✭✭✭✭✭✭

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • laura.buchanan
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!