Issue with Index Collect Formula

Kylie Jones

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!

Paul Newcome

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

Paul Newcome

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

Kylie Jones

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

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?

Kylie Jones

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.