Assigning an auto-number

brianw

Hi

I have a sheet that is populated via a form and if questions are answered in a certain way it copies cells to another sheet. Once the row has been copied to a the other sheet I have other update workflows that are triggered.

The problem I am having is I need to be able to assign a unique ID to the row being copied, but I cannot select the auto-number column type.

Any ideas?

Cleversheet

It's unclear whether you want the unique ID to be in (and from) Sheet A (where form results are collected) or only in Sheet B (where certain rows have been copied over).

If the former, create a new column in Sheet A called something like [Response ID] and designate it as a system-generated auto-number column, then include that column when porting the selected content over to Sheet B, where you've created a Text/Number field also called [Response ID].

If the latter, simply create a new column in Sheet B called something like [Response ID] and designate it as a system-generated auto-number column. If you then want Sheet A to show the ID that was assigned in Sheet B for rows that were copied to Sheet B, you'll need to identify some other unique aspect of that row* and use INDEX/MATCH to bring it back into Sheet A's Text/Number field called [Response ID].

* - One way to do that might be to create helper columns in both sheets called something like [Bring Back ID], and use a column formula to concatenate all the values on that row (assuming that yields a consistently unique result; if not, you might need to do the "former" action above in addition to the "latter' action—the first to enable you to identify each Sheet A row uniquely, and the second to enable you to use that unique ID to bring back the unique ID assigned when it got to Sheet B).

brianw

Sheet A has an auto-number column, but I need the row being copied over to Sheet B to have a different unique ID.

In Sheet B, Smartsheet wont allow me to insert an auto-number column for some reason. It is grayed out when I attempt to add the column.

Cleversheet

The only reason it would typically be grayed out is that a column with that system function has already been created on Sheet B. You can't have more than one system-generated auto-number column per sheet.

brianw

You just helped me realize why!?!

Its copying over the auto-number column from Sheet A already

Cleversheet

If you need separate IDs for Sheet A and Sheet B, you might try creating a [Sheet A ID] column in Sheet B, then copying the existing values over into that column, then delete the column they were in, save the sheet, and re-add the system-generated auto-number column named [Sheet B ID]. (Not positive this will work.)

If you need to show the [Sheet B ID] in Sheet A for any reason, you could use INDEX/MATCH to bring it back there from Sheet B.

If you've ended up with a satisfactory result from this query could you mark it Answered?

Hope it all goes well...

brianw

@Cleversheet I couldnt get that to work, but I did find a solution that worked.

On Sheet B I created a column with the verbiage for the Unique ID (Fire MOC) then added the formula (=row1) so the system would copy it down with each entry. Then added a column for a number count (=row1+1) then joined those columns to get the unique ID I wanted.

Final result being the Unique Sheet B ID (Fire MOC #) which goes up sequentially with each row addition.