Background: An International source Smartsheet has 1700 rows (and counting) and 85 columns. Using the Index/Match formula, we reference 14 columns of data within a new Regional Smartsheet. Why? Regions want to add additional Country-focused data, data that should not be managed on an International sheet that already has 85 columns. They want to reference 14 columns from the International Smartsheet but add another 30 columns of data. Index/Match is an excellent way to auto-pull data without having to tediously (and manually) find the row and column and adding a link to the cell.
Roadblock: The reference limit is 25000 data points. When we reference 14 columns, the total is 23,800 references. If we try to reference another column, the new total is 25,500, therefore putting us over the limit and the alert prevents us from adding another column reference.
Solution: We decided to reexamine the 14 columns referenced in the Regional sheet. We want to remove a reference and add a new reference. Therefore, we deleted the Index/Match formula with the reference. But that does not fix the issue, the referenced column is embedded in the code for the sheet. Even if I don’t use the reference in a formula, it’s still there.
Question 1: How can we remove an embedded reference to another sheet, so we can add a new reference? Please note, we cannot simply save the existing sheet as NEW because other Smartsheets manually link to Regional sheet data.
Question 2: We continually add data to the International sheet, after we add another 100 rows next year, equaling 1800 rows of data, the Regional sheet reference will include 25,200 cells of data. What will happen to the Regional sheet? Will all referenced columns error when the max 25,000 is reached?
Question 3: Using DataMesh, can I add the meshed data into the existing Regional Smartsheet?
Thanks for your help!