Formula Help - Index(Distinct(Collect



  • Candy Vonk


    I'm trying to do something similar to this situation. I'm new to Smartsheets and especially these formulas; I can't figure out where this result information is held?


    I can get the first row to work fine using this formula:

    =INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), 1)

    Which gives me the results I expected to see. I can change the 1 to a 2 and get the next owner. But I would like to automate the filling in of unique cells from the Smartsheet into a new list completing the entire list. I tried to use the second index but can't get the formula to work. I've tried so many combinations that I'm getting myself confused.

    Here is the formula I tried that is similar to the one in this post:

    =INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), 1+count({Owner POC}$1:[{Owner POC}1,"")

    Any help would be appreciated.

    Thanks in advance!!

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Candy Vonk

    The simple way would be to use a helper column and input the numbers 1,2,3 etc and reference that cell via helper@row. If you don't want that just reference another column that is already there and use the rows to count. Unfortunately, you will get a circular reference if you attempt to count the row the formula is in so just count any other one.

    =INDEX(DISTINCT(COLLECT({Owner POC}, {Owner POC}, <>""), {Owner POC}), COUNT(Primary$1:Primary@row))
  • Candy Vonk

    Hi @Devin Lee

    Thank you! That worked! I greatly appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!