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

  • AravindGP
    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)

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • brhea110891
    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • brhea110891
    brhea110891 ✭✭✭✭
    edited 04/18/24

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!