Is there a more efficient way to transfer an autonumber column to another sheet?

EnochSmith

Hi all,

I can go in-depth into our workflow, but to keep things simple, my organization is using VLOOKUP formulas to pull info from a repository sheet, send out a survey to a respondent, then update our sheet with any new info received from the respondent.

We occasionally have large influxes of data, so every column counts, since we often bump into the maximum cell count Smartsheet sheets can handle.

The way we've been matching rows to each other is by using an autonumber column to ensure they each have a distinct row ID. However, the autonumber row IDs don't transfer from one sheet to another, so we've had to make a helper column to ensure the row ID stays static when moved across sheets.

That's created another issue where the static row ID is often changed to a text field rather than a number, which breaks the VLOOKUP formulas. So, we've had to create a 2nd helper column that sets the 1st helper column to always be a number.

In theory, at least, this doesn't seem like it should require more than a single column. What would be ideal for us is a column that is autonumbered in our case creation sheet, then stays that number (and stays a number) when transferred across sheets, or when reported in a survey. Does anyone have any clever ideas for how to reduce the number of columns for this process (ideally to just one)?

Paul Newcome

How exactly are you converting it into a number?

EnochSmith

=VALUE([Row ID]@row)

Paul Newcome

Ok. Try dropping the formula you have in [Row ID] in place of "[Row ID]@row".

EnochSmith

Hi Paul,

Thanks for the help. To be more clear, I have 3 columns: "Row Gen", which is my Autonumber column; "Row ID", which collects the results of the forms; and "Row IDV", which processes the results of the forms into a number field. Is there a way to insert a formula into the product of a form?

Paul Newcome

Are you able to post a screenshot for reference?

EnochSmith

Hi Paul,

I've attached 4 screenshots.

The first 2 are from the first sheet (the CASE REPORTING FORM). An employee uses a form to enter any cases they receive there. A 2nd employee assigns the case a number, and uses a workflow to transfer the to a set of district-specific sheets.

In the district-specific sheets (each has a REPO, POSITIVE, and TERMINAL sheet), the workflow copies the case to the REPO sheet and moves it to the TERMINAL sheet. The 3rd screenshot shows the formula used to ensure the "Row IDV" column is a number.

In the TERMINAL sheet, a 3rd employee checks the case for any errors, and uses a workflow to send a form to a member of the public. The form records the data in the POSITIVE sheet, attaching the "Row ID" number to the case. (Note: only the TERMINAL and CASE REPORTING FORM are directly edited by employees).

The 4th and last photo shows the VLOOKUP formula I've been using to pull the data back to the TERMINAL sheet. Essentially, the logic there is "if the cell from the POSITIVE sheet is empty, then report the data from the REPO sheet; if the cell from the POSITIVE sheet is not empty, then override the data from the REPO sheet with the data from the POSITIVE sheet." We want any data entered by the member of the public to override our own entries, since they normally know their birthdates, test and onset dates, name spellings, etc. better than we do.

During the Omicron outbreak, we were often receiving 1000+ cases per week for each district, and it can take 3-4 weeks to resolve a case, so that's why we're trying to limit the number of columns as much as possible - at the time, we needed over 100 columns to fit all of our survey data, which meant that sometimes we could only fit 2 or 3 weeks of responses, and were having to manually move some cases to separate sheets, which caused issues for our workflows.

Paul Newcome

Right. So if you change the VALUE formula to reference the Row ID Gen column directly you can get rid of the Row ID column.

EnochSmith

Hi Paul,

The issue I've had there is that in order to transfer the rows from one sheet to another, Row ID Gen has to be an autonumber field. Since the cases aren't being filed in the same order, that results in Row ID Gen assigning distinct numbers to each case depending on which sheet it's on, which makes it unsuitable as an identifier.