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
-
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.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!