INDEX/MATCH COLLECT #UNPARSEABLE

Options

Hi Smartsheet Team!


I would love some help. We have a sheet[TESTING] that has a list of our 900 stores. As these stores are closing/reopening they are submitting forms that get added to the bottom of TESTING.


What we want is to be able to constantly have the information from the forms being submitted pulled up into the listing of our 900 stores. We are unable to do this on 2 separate sheets because it exceeds the cross-reference limit.


I have followed the steps in this forum: https://community.smartsheet.com/discussion/53411/index-match-max-collect and still am unable to write the formula so it works. What we don't want is to have to lock in cell reference because then as new forms are added it won't include them.

The current formula is:

=INDEX([Email of Person Reporting]$948:[Email of Person Reporting]1900, MATCH([Max Row]@row, [Row Number]$948:[Row Number]1900, 0))

However, once I drag that down 85 cells I start getting #UNPARSEABLE unless I add in the $(See Below)

=INDEX([Email of Person Reporting]$948:[Email of Person Reporting]$1985, MATCH([Max Row]@row, [Row Number]$948:[Row Number]$1985, 0))

I am at a loss for how to alleviate this issue and would LOVE some help!!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Kathryn,

    Can't you reference the whole columns instead?

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Kathryn Bailey
    Options

    Hey Andree!


    Thanks for the quick reply. When I reference the whole column, it works for the top row but as soon as I drag down I get #Blocked in first cell and the rest say #Circular Reference.

    If you look here, I am trying to fill the Email of Person Reporting Column.

    Here is the formula I am using.. and here is what happens when I drag it down.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Kathryn Bailey

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!