INDEX/COLLECT - #UNPARSEABLE

jctruzzolino
jctruzzolino ✭✭
edited 04/17/23 in Formulas and Functions

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!