I'm working on an exercise to reconcile all of the freelancers my company has submitted through our Smartsheet freelance request form compared to those that are currently active in our HRIS system.

We have many submissions for the same freelancers in our request form, as they're repeatedly extended or rehired, however i'm looking to reference info from the most recent request for each of these folks.

In the separate sheet I created for this reconciliation exercise, I was able to pull the most recent start date for the recurring freelancers using the following formula (and the same for end dates): =MAX(COLLECT({FL Start Date Ref}, {Employee ID FL Tracker Ref}, [Employee ID (WD)]@row))

What i'm now looking to do is pull through their most recent "Contract Status" (i.e. Pending Signature, Active, Expired, etc.) that matches up with their most recent start/end dates. I've tried the following formula:

=INDEX(COLLECT({Contract Status FL Tracker Ref}, {Employee ID FL Tracker Ref}, [Employee ID (WD)]@row, {FL Start Date Ref}, [Start Date (FL Tracker)]@row, {FL End Date Ref}, [End Date (FL Tracker)]@row),1)

I cant seem to figure out why i'm getting #UNPARSEABLE with the above.

Any help or suggestions would be greatly appreciated!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are no syntax issues. The only thing I can think of that would be causing that would be if there is a column name that doesn't match what is being used in the sheet. Try clicking on the cell you want to reference instead of typing out the column names.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!