INDEX/MATCH COLLECT #UNPARSEABLE

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!