Maxing Out Cross Reference Sheet
![Ann Foley](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi!
I'm using an Index Match formula to pull info into a roll up sheet:
=SUMIF({AC 2020 Priority Hotels}, "Complete", {AC Jan ITR}) / COUNTIF({AC 2020 Priority Hotels}, "Complete")
However, I'm getting this error message:
The max is around 25K cells which I definitely don't have in my sheets. However, when I cross reference a sheet (AC 2020 Priority Hotels) and choose the column (AC Jan ITR), it's picking every single cell in that column....when I only actually have data in 150 cells.
So, my question is - I'm maxing out the cross sheet references because it's picking every cell in my 60 columns - so how can I just pick certain cells within column and not the entire column?
Thanks!!
Best Answer
-
Happy to help!
Note sure why!
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.
Answers
-
Hi Ann,
I think you’ve reached the 100 distinct cross-sheet reference limit.
Could that be it?
More info:
- Each sheet can include no more than 100 distinct cross-sheet references.
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help 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.
-
Thanks, Andrée. Perhaps that is the problem - but I'm confused why I was able to complete one set of sheets just fine and without error, yet when I try to replicate the same exact thing on another set of sheets (but with different data), I'm running into this error. Any thoughts? Thanks!!
-
Happy to help!
Note sure why!
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.
-
Thank you so much! For some reason, I'm no longer getting that error and it works just fine!
-
Excellent!
I'm always happy to help!
Glad to hear it!
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.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!