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)?



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!