IF + INDEX COLLECT Formula
I am requesting help writing a formula.
Currently I am using this formula: =INDEX(COLLECT({202324 Step 1: SelfAssessment Form Step1 Option}, {202324 Step 1: SelfAssessment Form District}, District@row, {202324 Step 1: SelfAssessment Form School2}, School@row), 1)
I would like to update it so that based on a Yearrange in one column, the Index collect would go to the year specific sheet. How would I write the formula?
Thank you.
Answers

Hi @brhea110891
Index collect is used to pull a value based on multiple criteria. If you've the Revalidation Year column in your source, your can include that to your formula
=INDEX(COLLECT({202324 Step 1: SelfAssessment Form Step1 Option}, {202324 Step 1: SelfAssessment Form District}, District@row, {202324 Step 1: SelfAssessment Form School2}, School@row, {Revalidation Year column in source}, [Revalidation Year]@row), 1)
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training

HELP  I tried to modify the formula in a different column and then my original column and formula now say invalid reference. I copied back my original formula and it is not working. I don't know how to fix.

If you need the INDEX/COLLECT to look at a different sheet based on the year, you would need to write out individual INDEX/COLLECT formulas and drop them into a nested IF statement.
=IF(Year@row = 2024, INDEX(COLLECT(2024 sheet ranges), 1), IF(Year@row = 2023, INDEX(COLLECT(2023 sheet ranges), 1), INDEX(COLLECT(2022 sheet ranges), 1)))
The #INVALID REF error indicates that you have entered a {Range} that was not properly set up. Double check all of your {Cross Sheet References} and ensure they have all been set up following the appropriate steps for creation.

Thank you. After several times, we figured it out. I found a video online for Index Collect that was very helpful. One of my biggest struggles is knowing where/when to open and close parenthesis, when a space an coma is needed. I think we made a few mistakes with this as we were trying to rebuild the formula.
Also, it was odd that at the same time we got a message about "Smartsheet not being reachable at this time." or something like that.
Thanks again for responding quickly.
Help Article Resources
Categories
Check out the Formula Handbook template!