INDEX/MATCH COLLECT #UNPARSEABLE

Kathryn Bailey

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!!

Andrée Starå

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!

Kathryn Bailey

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å

@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)