Invalid operation using index & collect function

Hi Smartsheet Community,

I'm trying to perform cross-sheet references, specifically pulling the names of accounts from a deal tracker sheet based on specific employee names, using both the 'index' and 'collect' function, with one 'if' statement:

=IF({Account Status in deal tracker} = "Live", INDEX(COLLECT({Account Name from deal tracker}, {Employee Name from deal tracker}, "Insert specific name"), 1))

That is, my goal is to populate a cell with the first entry of this collected list, as long as the account status is live, and that the specific person can be identified.

Smartsheet is currently spitting out '#INVALID OPERATION.' Any speculations as to why this is (I suspect it has to do with the latter portion of the formula)?

Thanks,

Will

Find more posts tagged with

Accepted answers

Mathieu PERSICO

Hi Will,

Could you give us a screenshot to have a visual of what you are dealing with? You can remove or replace the confidential data if you have any.

Mathieu @evolytion

All comments

Mathieu PERSICO

Hi Will,

Could you give us a screenshot to have a visual of what you are dealing with? You can remove or replace the confidential data if you have any.

Mathieu @evolytion

Will Schmetterer

Hi Mathieu,

I sincerely appreciate you responding, but please do forgive me -- I was able to figure out a solution. Feel free to close this thread :)

Best,

Will

Andrée Starå

Hi @Will Schmetterer

What was the solution?

Be safe and have a fantastic weekend!

Best,

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

Will Schmetterer

Hi @Andrée Starå,

I ended up omitting the 'IF' statement and placed it into the 'Collect' function. It ended up looking like:

INDEX(COLLECT({Account Name from deal tracker}, {Employee Name from deal tracker}, "Insert specific name", {Account Status in Deal Tracker}, "Live"),1).

Candidly, I'm not sure why this worked versus my first attempt. I certainly could have mis-typed a formula entry on my end.

Stay safe as well :)

-Will

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.