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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.