IF + INDEX COLLECT Formula

brhea110891

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.

AravindGP

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)

brhea110891

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.

Paul Newcome

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.

brhea110891

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.