Help to create a Formula!

System

This discussion was created from comments split from: Can I get 2 auto-number columns?.

Abe B.

What would the formula be for creating a unique row number in this this helper column?

Paul Newcome

@Abe B. Are you able to provide more context?

Abe B.

@Paul Newcome

Hi Paul and thanks for reaching out!

Here's my detailed case and challenge that I encounter:

We have a major and highly active Smartsheet "SS" intake form that eventually resides in one repository. I had to create a new separate sheet to only pull specific data/ columns from that huge original repository, where many team members can track their own assigned requests on this new sheet. I have done this after contacting SS and scheduled a call with one of their experts who helped me create a great sheet using (IF/INDEX/MATCH…etc) which I was never familiar with. However, one pre-condition for creating this index/match formula in the new sheet was the need for a unique identifier column in both sheets, in order to reference my formula to. So the way we grabbed this column from the source to the destination sheet was through "Link from cell in another sheet" option, which pulled all these unique values from the source sheet (Originally created in an Auto-Numbering column). This is where I specifically having challenge in.

Now, after couple of weeks of creating this nice sheet, this specific "unique identifier" column has stopped pulling these numbers from the source to destination sheet, which hindered the entire formulas in all columns from working and pulling data from source. I just learned from SS "Cell Linking" page that this feature only allows you to pull a max of 500 cells, which is nothing for our heavy traffic for both sheets. To make it even worse, I was just told by SS expert help desk that due to what they refer to as "Merge Save" in the source sheet, this will impact the cell linking between sheets whenever multiple people work on the same source sheet and do multiple savings "Which honestly threw me off".

I considered Data Mesh as a workaround, but ran into the same problem as it requires a unique identifier column to measure against and pull data.

The only way I left up with is to create an identical auto-numbering column in each sheet, but as I mentioned above, I already have an auto-numbering column in the source sheet and therefore I need to cheat on the page and somehow create auto-numbering system for each single row by using a formula.

Please let me know if that helps understanding the issue, and let me know if you have any questions

Abe B.

@Paul Newcome

I just posted a long detailed reply but it's not yet showing up here… Any thoughts?

Paul Newcome

Is there a reason you are wanting to use a separate sheet for the other users as opposed to a row report?

Abe B.

As it is a busy sheet that contains different categories and types of requests (Over 50 different scenarios), each of our team members will be able to use the filter on the sheet to filter down only their designated and assigned requests, their status …etc. Where in a row report I will have to create a generic filter for the entire team, and if I give them edit access to change the filtering options in the report, they most likely will mess up the entire report as it's a standard Filtering there vs the sheet. The sheet is read only for all team member, but they still can filter to their assigned requests

Paul Newcome

What about adding their input columns into the source sheet and having them work there?

The challenge with using formulas to pull data from one sheet to another where the second (recipient) sheet also has manual entry is that if the source sheet gets sorted, has rows added at the top or in the middle, or has rows deleted, the manually entered data on the recipient sheet does not move with the data being brought over. It creates massive challenges.

Do you have access to the premium add-on Dynamic View? This would be a perfect use case for it.

Abe B.

Thanks again Paul!

So the second/recipient sheet is totally Read only sheet and no entries are allowed there, it only pulls 11 columns from the source (over 55 columns). The source sheet is being managed by a different "smaller team 2-3 members" who work on different level of requests once received and triage only those requests pertaining the other 40 team member to the second sheet.

Yes, I do have a dynamic view and was considering it, but since we started and announced that second sheet with the team, I deeply hate to keep changing tools and mechanism and then run trainings on how to use Dynamic view and filter their own designated requests…

Paul Newcome

Technically it can be done with formulas, but honestly it is tedious to build, doesn't scale well, and requires manual entry on your part during the build.

I very strongly suggest Dynamic View since you have access to it. I understand that a sheet has already been announced, but depending on your delivery, you can make it a relatively straightforward transition to a DV (drop it into a dashboard using a web content widget so they don't have to navigate to a different app), and DV is relatively straightforward for filtering and sorting. Filtering is pretty much the same as in a sheet, and sorting is more user friendly.

Abe B.

I hear you Paul. Let me talk with the team and see if we can shift for the last time to Dynamic view, hope I get the green light and build it quick as it will be a read only tool.

At least I know there is no easy workaround for this now. Thanks again for your help and addressing my concerns