Why does my Index/Collect Formula stop working after 8 rows?
Hello,
my formula stops working after it gets through 8 rows. I have a picture below where I'm just testing out 30 rows but I need this to work for about 4000 rows, however I can't figure out why it gives me an error after awhile.
This is my formula.
=INDEX(COLLECT($ItemCategory$1:$ItemCategory$30, $[Date Order]$1:$[Date Order]$30, [Date Order]1), [Row ID]1)
A working formula will help me build out a larger report that I am working on.
Best Answer

@J.C. Benny So the formula isn't working because of the index part of it. It's trying to collect data that match criteria and then indexing the index value/number within that collected data.
So on row 9 you're saying "Show me the Item Category, of the 9th row that is in date order 20245. If you count there's only 8 of those.
So either that's correct and you just need to wrap that formula in an iferror()… or your logic is wrong. Also I would advise changing your specific row references to @rows etc so you can make that a column formula… if you solve the logic.
Answers

@J.C. Benny So the formula isn't working because of the index part of it. It's trying to collect data that match criteria and then indexing the index value/number within that collected data.
So on row 9 you're saying "Show me the Item Category, of the 9th row that is in date order 20245. If you count there's only 8 of those.
So either that's correct and you just need to wrap that formula in an iferror()… or your logic is wrong. Also I would advise changing your specific row references to @rows etc so you can make that a column formula… if you solve the logic.

@Matt LynnPCG Thank you for explanation.
I somewhat understand what you are saying but I don't fully understand how best to fix it.
I'm working on some edits but not having much success.
Edit: I actually just wrapped in a simple iferror formula like you said instead of trying to do anything too complex and looks to work just fine, thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!