Can Only reference a total of 100000 cells (combined) from other sheets. Please select fewer cells a

Benie

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:

Colleen Patterson

@Benie

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.