Issue with Index Collect Formula

Looking for help! We collect form responses that load onto this sheet below the KPI section. I need to see, at a glance, TODAY'S responses in the Reporting Data section. So I'm using an INDEX COLLECT formula to pull in today's response from each team member. However, I am getting the errors as shown below. I simply want to display the data submitted for each person.

Can someone help please? Thank you!


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It may be that you are trying to compare a date value to a text value. Try replacing the Type$3 reference with TODAY().

  • Thanks @Paul Newcome, that worked, obviously only for the users who have submitted the form with today's date.

    However I have a few other challenges, I need to replicate this for the next few columns, which works fine for the first user I have input the formulas for. However I get the CIRCULAR REFERENCE error when applying these formulas to other rows.

    What do I need to do to avoid this error? See attached.

    Once I have this fixed, I will have another question on whether the cells can be blank if there has been no entry for TODAY.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are not going to be able to put the formula in a cell that the formula is referencing. Is there a reason you do not use a separate sheet for this or a report with grouping/summary enabled?

  • No there is no particular reason for not using a report, I think it may solve this issue. I'm wanting to create pretty much an ENPS dashboard but I don't seem to have the flexibility with Smartsheet so may have to link up to MS Power BI. Unless there are any suggestions for how to have a dashboard displaying this realtime.

    There are 4 key metrics I want to track over time, but not sure if it's possible. The main one being the average daily "My Score Today" and how that changes over time on a weekly basis.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!