How to link a column to another sheet so I can run a formula on duplicate values?

Craig Duffy

Is there a way to link a whole column to another sheet? I need a whole column linked, not just cells. Copy and pasting 500 cells at a time is not going to work, as it will not update if rows are added or deleted.

I considered a report, however that would only accomplish part of the job, as I want to filter the results. I dont think there is a way to flag duplicates in a report.

What I am looking for is to see a list of all duplicates from a single column. I tried doing that as a formula in the original spreadsheet, however it became unmanageable as the formula was comparing values from about 4000 rows and it slowed down the sheet so much it was unuseable.

So here is what I need, broken down:

1. See all duplicate values (words, not numbers) in a single column

2. The values in this column, as well as the number of rows in the column, is going to change over time- so linking individual cells to a new smartsheet is not going to work. It needs to update dynamically

3. The Duplicates can be reported in a report or separate smartsheet in order to keep the original document clean and responsive. Keeping the formula in the original file was causing it to slow down too much.

a. The formula I have been using is a variation on this one- there may be a more efficient way to do this that I do not know of.

i. https://community.smartsheet.com/discussion/duplicate-values-conditional-formatting


4. This duplicate list will be accessed and reviewed on a regular basis; but not every day or every week- so if it takes a long time to load and review, that is fine. We will just deal with it. As long as it automatically updates with all values from the column, we will be fine.

Any ideas on how to do this?

Thanks for any help!

Jim Hook

Hi Craig,

 

While you can't link a complete column in one operation, you can link all cells in a column 500 at a time (limit is 5000 incoming links per sheet). Then all the cells in that column will automatically update anytime the cells in the original column change. I do this frequently and then have formulas in another area on the destination sheet that can check for duplicates or combine similar data that comes in on multiple rows.

 

I have experienced the slowdown you mentioned when trying to do everything on one sheet. I have one application that takes all the time charged on up to 100 active projects by our 16 person company and computes the financials to date on each project. I ended up doing this on two Smartsheets after doing it all on one started taking two minutes, or timing out, to save changes. The first sheet validates that all the entries are for valid project names and formats the data for the second sheet that takes all the hours for all the different projects and work types (all by links) and applies the correct burdened rates to come up with a project financial summary.

Craig Duffy

Thanks Jim-

 

What happens when new fields are addeed? Do I need to update the linked sheet? Im trying to avoid having to do manual updates when rows are added/deleted from the original sheet

Jim Hook

Craig,

 

I think that adding/deleting rows in the source sheet in the middle of the linked-in area will mess things up as you mention. The way I work around this is to create a special area in my destination sheet where I manually copy/paste the data from the original sheet. Then I have my formulas analyze the entire special area. It does require a manual step but that only takes me about 15 seconds. Just make sure that when you paste the new data into the destination sheet that any old data is cleared first since if you paste new data over the old data, and the new data has fewer rows than the old data, some old data will remain in the special area and be part of the analysis.

Craig Duffy

Thank Jim- 

 

That sounds like the best plan given the limits of Smartsheet.

 

Thanks for your help

Amro

In my case, I'm using a web form to add data to the sheet, and Smartsheet considers any linked cell as an occupied cell. therefore, it adds the new data after the linked cell!!

So linking column is going to be very helpful 

 

Thanks

Unknown

This content has been removed.

Matt M

Yes, I agree. It doesn't make sense why Smartsheet users cannot link to cells in a column unless data already exist in the cells.

Smartsheet is powerful in some ways but it always seems like you have to be very creative to get things to work like you want a lot of the time...at least when it comes to completely automating your work.

Jim Hook

I just tried something new that might solve the problem this discussion has been talking about since 2016. The new cross-sheet reference capability in Smartsheet allows a range on another sheet to be an entire column. I just did a simple COUNT function on another sheet that referenced an entire column on another sheet. The first time it counted the original 16 non-blank cells. Then I went to the new sheet and added many more new cells. Going back to the sheet with the COUNT function the total was now 50 and the little triangles show that even the new rows below the end of my filled cells are included in the range. This increases the total number of cells one sheet can reference from another sheet from 5000 to 25000. Still not as many cells as Excel can reference but a major improvement.

Does this help with the problem?

Brandi Christie

I have been able to link columns by doing the following:

1. Click in the cell you want to link to
2. Click the "Cell Linking" Icon
3. Select the data source
4. Click on the Gray header bar of the column you want to link to (this is the key to making it work)
5. Click Create Link

This seems to be working fine, but let me know if you run into any problems

latsyrcg3

Thanks for your help! I have been wondering how to do that!