INDEX/COLLECT - #UNPARSEABLE

jctruzzolino

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!

Paul Newcome

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.

Paul Newcome

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.

jctruzzolino

Just tried again by clicking on the cells I was referencing, instead of typing out the column names (as I was doing before), and that worked!

Thanks so much!

Paul Newcome

Happy to help. 👍️

JLuke

I have a formula that works in one sheet however, if I copy and paste it into another sheet it does not work. Gives me the #UNPARSABLE error.

=INDEX([Current Task]3:[Task Name]95, MATCH(true, [Current Task]3:[Current Task]95), 8)

JLuke

https://community.smartsheet.com/discussion/comment/372130#Comment_372130

Everytime I try and select the cells, I exit from the area where I enter the formulas.???