Can Only reference a total of 100000 cells (combined) from other sheets. Please select fewer cells a
Hi All
I am a newbie to Smartsheet, been working on it for a few months only. I am getting this error. Google is not helping me much.
Please can a good samaritan assist me in this forum? Thank you so much.
Existing formula that I am trying to update =COUNTIFS({Archive Sales Stage}, [Primary Column]@row, {Archive BU}, $[Primary Column]$1, {ArchiveFiscal}, "FY22H2"
The error I am getting when I try to include the Fiscal column in the formula:
Answers
-
This error is telling you that the sheet you are working on is pulling in too many cell link references. How many other fields are also using cell links? I ran into this issue, and started a "helper" sheet, where I do some of my calculations / references, to reduce the data load on the sheet. You can find more information on cell linking here: https://help.smartsheet.com/articles/861579-cell-linking#:~:text=Typically%2C%20when%20you%20link%20a,mean%20your%20link%20is%20inactive.
An alternate option would be to convert your VLOOKUPS over to INDEX/MATCH formulas which could potentially save a significant number of references.
@Paul Newcome provided a great answer to a similar question in a prior post which aligns to my same thinking, that stated:
You can however convert your VLOOKUPS over to INDEX/MATCH formulas which could potentially save A LOT of references.
INDEX/MATCH only references the two target columns. The one to pull and the one to match on. You don't have to reference the other 138 columns in between. Plus since you are referencing the two columns separately, they can be moved around and it doesn't matter which is on the left and which is on the right within the source sheet.
=VLOOKUP([Column Name]@row, {Range of 140 Columns}, number, false)
=INDEX({1 Column To Pull From}, MATCH([Column Name]@row, {1 Column To Match On}, 0))
The breakdown:
INDEX pulls from a column based on a specified row number:
=INDEX(column_to_pull_from, row_number_to_pull)
MATCH outputs a row number based on where within a column the data being searched for is found.
=MATCH(value_to_match, column_to_find_match_in, 0)
So we use the MATCH to output the row number where the match was found and use that automated number to tell the INDEX function which row to pull from.
It is more flexible, more efficient, and requires less cells to be referenced since we don't need to include all of the stuff in the middle.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!