IF + INDEX COLLECT Formula
I am requesting help writing a formula.
Currently I am using this formula: =INDEX(COLLECT({2023-24 Step 1: Self-Assessment Form Step1 Option}, {2023-24 Step 1: Self-Assessment Form District}, District@row, {2023-24 Step 1: Self-Assessment Form School2}, School@row), 1)
I would like to update it so that based on a Year-range 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({2023-24 Step 1: Self-Assessment Form Step1 Option}, {2023-24 Step 1: Self-Assessment Form District}, District@row, {2023-24 Step 1: Self-Assessment 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!