How can I remove a sheet reference, so I do not receive the alert ‘Reference a total of 25000 cells

EmilyH

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!

Nick Burrus

If you use columns the individual cells aren't counted as 1 reference each. 

There is no way to delete a reference right now.

I use over 27,000 references in a single sheet no error yet.

Paul Newcome

One option would be to add another column to the master sheet where you join the data from various columns. Then on your target sheet, you would pull the data from that joined column and parse it out essentially duplicating various data points on your target sheet from the source sheet.

 

You can then use same sheet references. You wouldn't have to do this for every single column. If you join 3 columns, you have just opened up 2 more column references available for cross sheet references.

 

Does that make sense?

EmilyH

Yes, I understand. Excellent idea.

1. Use the “Join” formula to join multiple column data into one row cell on Master Sheet. Add a comma to separate column data.

2. Use Index/Match to reference the column with the “Joined” value.

Since the character length is different for each joined column, how can I separate data on the target sheet?

For example, joined cells might contain:

Jo, Smith, England

Samantha, Newcome, México

What formula can I use to separate the cell into three columns on the target sheet?

Also, since the source sheet has 1700 rows, and since I have referenced the data from 14 columns, can I change and add the new joined column to the existing sheet. In other words, can I fix after the fact? Is it too late since I can’t delete a reference? Any new references within this sheet must be manually linked to a specific data cell?

Note: The target sheet needs 700 more data points from the source sheet. The vLookup or Index/Match formula would take maybe 10 minutes of prep time. If I have to manually link (copy Project ID, find specific Project ID row, then scroll to column 50 to link to the cell) will take approximately 2 mins per transaction. Therefore ~23 hours to manually link data into a Smartsheet. Any idea when Smartsheet will increase the 25000 reference?

Thanks again for your help.

Paul Newcome

To pull data from in between columns, you have a few different options. My recommendation would be to pull the entire string over to the new sheet, then create a separate column for each data point to be referenced.

 

The general idea would then be to use a MID function to pull data from the middle of the string and the FIND function to look for the placement of the commas to give a start and finish point in your MID statement.

 

The more specific setup is a little more tedious and detailed, but once in place remains automated. I have a solution built for parsing out data down a column already, but I will need to build out a quick solution for pulling it across a row. I already know how I want to do it. It's just a matter of putting it together and testing it. I'll get back to you once I have an example built out.

Paul Newcome

Ok. So once I started the build, I realized my previous statement of using MID functions was incorrect. I used a SUBSTITUTE and JOIN function to essentially remove what had already been parsed out.

 

Here are two links.

SOURCE SHEET

TARGET SHEET

.

You will see how I was able to replicate the sheet data based on the Name column. Obviously you can pick and choose which columns are pulled  and what type of data you are using to base the pull off of to make it suit your need, but this is going to be the general idea.

 

All you will need to do is pick a single UNIQUE data point to manually enter on the source sheet, and all other pertinent data will automatically be pulled through.

.

To completely remove a cross sheet reference though, I have heard tell that unused references are automatically deleted after a set amount of time. I can't remember where I read that, but I will see if I can find it and verify.

 

Otherwise you will need to manually remove ALL references in one fell swoop by going to the source sheet and clicking on one of the linked cells. You will see a blue box that pops up saying 

 

"Used in formula on: Sheet Name          delete"

 

Clicking on the delete option will remove ALL cross sheet references to the target sheet from the source sheet.

 

While this may seem rather daunting... All you would need to do to fix this is update one instance of each cross sheet reference on the target sheet then save. The rest of the instances of that particular reference will automatically update as soon as you save and refresh.

 

You can also use this to change exactly what range is being referenced when updating. You can then go back in and edit the reference afterwards to change the name of the cross sheet reference if needed.

 

If you would like more info on how this works, let me know, and I will put together some screenshots for you.

EmilyH

This is a great idea and it works however I have a few more questions:

1. Within the Source sheet, there are a few #NO MATCH, #INVALID DATA TYPE, and #INVALID OPERATION cell values in some rows. I noticed that if I use the JOIN function, the default result is one of the #errors because one cell in the row contains an #error. Is there anyway to JOIN data even if one of the cells contains an error?

Note: I also tried manually adding up row data (example: NAME1 + “/” + Dept1 + “/” + Title1) but the cell with the #error created an #error sum/result.

There is a valid and just reason why a cell value might contain an error message. This cannot be changed or corrected because we are waiting on additional data. Is my only option to add the column data and exclude the columns with #error?

2. Within the Target sheet, I don’t want to display all row data, just NAME and FullPart columns. What would the LEFT(SUBSTITUTE…) formula look like for the FullPart column if the other columns (Dept, Title, etc) are missing?

3. Great idea about removing references, makes total sense. I just have to find the reference on the Source sheet that I am not using on the Target sheet, then delete. If you can find out if unused references are automatically deleted after a set amount of time, that would be good to know.

Thank you so much for your time!

Paul Newcome

This is a great idea and it works however I have a few more questions:

 

I'll see what I can do...

.

1. Within the Source sheet, there are a few #NO MATCH, #INVALID DATA TYPE, and #INVALID OPERATION cell values in some rows. I noticed that if I use the JOIN function, the default result is one of the #errors because one cell in the row contains an #error. Is there anyway to JOIN data even if one of the cells contains an error?

Note: I also tried manually adding up row data (example: NAME1 + “/” + Dept1 + “/” + Title1) but the cell with the #error created an #error sum/result.

There is a valid and just reason why a cell value might contain an error message. This cannot be changed or corrected because we are waiting on additional data. Is my only option to add the column data and exclude the columns with #error?

 

I would honestly suggest using an IFERROR function to replace the error with some kind of text such as "Incomplete Data" or "Missing Data" or something like that. That way you can still pull everything without the error causing the entire row to be unusable.

.

2. Within the Target sheet, I don’t want to display all row data, just NAME and FullPart columns. What would the LEFT(SUBSTITUTE…) formula look like for the FullPart column if the other columns (Dept, Title, etc) are missing?

 

Both the target and source sheets would need to be setup to account for only the columns you want to display on your target sheet. If you only want specific columns, you would need to either use what you suggest in #1 where you use specific [Column Name]@row references or you would need to put those columns next to each other to be able to use a JOIN statement. You would then only replicate those columns on your target sheet.

 

The beauty of the examples I provided is that they can be scaled to fit whatever you need. If you only want to display 3 columns, then only join those 3 columns on the source sheet and only replicate those 3 columns on the target sheet.

 

So based on only wanting the Name and FullPart columns, you would only join those two together on the source sheet, and you would only replicate those two columns on the target sheet.

 

Does that make sense?

.

3. Great idea about removing references, makes total sense. I just have to find the reference on the Source sheet that I am not using on the Target sheet, then delete. If you can find out if unused references are automatically deleted after a set amount of time, that would be good to know.

 

Removing a reference from the source sheet removes ALL references. You would then have to go back to the target sheet and basically "reset" the references you wanted to keep. You would only need to do this once per reference kept though. Once you go back to the target sheet and redo the {Source Sheet Range 1}, all other instances on the target sheet of {Source Sheet Range 1} will automatically update as soon as you save. You would then do the same for {Source Sheet Range 2}, {Source Sheet Range 3}, etc. only re-linking one instance and the other instances will automatically update themselves.

 

I am still looking into the timing thing. I know I read it in a post here in the community somewhere, but I am having trouble tracking it down.

.

Thank you so much for your time!

 

Happy to help! 

Paul Newcome

How are you able to exceed the 25,000 cell limit?

EmilyH

You have addressed all my concerns and answered all my questions.

Every day I am learning something new within Smartsheet. Thank you so much for your help!

Emily

Paul Newcome

Happy to help! 

 

Any other questions come up, don't hesitate to either follow up here if related or start a new discussion.

dsmartsheetuser

Hi Paul,

I am running into the same issue of 25000 cell references. I tried your JOIN/SUBSTITUTE formula and getting circular reference error. I have similar set up of sheet

Target Sheet: ID#, Item Name, Product_Type, Product_Category

=LEFT(SUBSTITUTE($JOIN@row, JOIN($[product_type]@row:[product_type]@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($[product_type]@row:[product_type]@row, "/") + "/", "")) - 1).

Any help is much appreciated!

Thank you,

Deepthi

Paul Newcome

Are you able to provide screenshots? The #CIRCULAR REFERENCE error means the either you are referencing the cell the formula is in, or you have two formulas referencing each other.

dsmartsheetuser

Target_Sheet.PNG

Hi Paul,

Thank you. Please find attached screenshot of the sheet.

Thank you,

Deepthi

Paul Newcome

The BOLD portions of the formula are what is causing your circular reference. You are referencing the cell that the formula is in.

=LEFT(SUBSTITUTE($JOIN@row, JOIN($[product_type]@row:[product_type]@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($[product_type]@row:[product_type]@row, "/") + "/", "")) - 1)

You will need to correct these portions.

dsmartsheetuser

Thank you! I'll change it and see.