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 GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 377 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!