Auto Number based on criteria

Caleb W

I have a sheet that we use to track all of our out of scope tasks that will be priced as Change Orders. All of our jobs are tracked in one sheet and are either FFP or WAF when entered. Once they are approved they move to another sheet so the formulas do not change the pricing.

Is there a way to auto number based on Job Code and FFP or WAF? I have some in there that sare up to 68, so I would those to start at the next numnber in sequence. Each job starts at 1.

Jason Albrecht

Hi @Caleb W

If I understand your request, you'd like a formula that looks at the [Project Code] (Job code?) column and the [Type of CO] column to then put the next relevant number in sequence in the [WAF # / CO #] column?

If so, I assume then, that if the next task that comes in has a project code of KR-GOR1A and is Type WAF, the next WAF # would be 9. But if the project code is KR-DC2 and the Type is WAF, the next WAF # would be 69?

If so, in the [WAF # / CO #] column, you could use the following (as long as each of the entries are numbers... i.e., 001 is not recognised as a number, neither is 08, etc.):

=MAX(COLLECT([WAF # / CO #]$1:[WAF # / CO #]11, [Type of CO]$1:[Type of CO]11, [Type of CO]@row, [Project Code]$1:[Project Code]11, [Project Code]@row)) + 1

Caleb W

@Jason Albrecht,

That is exactly what I am trying to accomplish!

When I enter the formula listed above I get a #CIRCULAR REFERENCE error.

I went through and adjusted to all recognizable numbers as you mentioned.

Jason Albrecht

Hi @Caleb W,

Sorry, I should have mentioned that the example above was in row 12, with references starting from row 1 to 11.

You'll notice row 1 is fixed, with a $ sign in front (assuming new data enters at the bottom of the list).

So, for each new entry, you can simply drag the formula down from the cell above (by click-hold-drag down on the bottom right corner of the cell).

In short, when pasting the formula into the current last row, change the number 11 to the row number above and then drag the formula down each time a new task is entered.

Caleb W

@Jason Albrecht,

Thank you for the quick reply!

What, if anything would I need to change as I have my new data coming in at the top?

The other part would be since the data is being entered via form, would the formula auto number that as it comes in, or would I still need to go drag the formula.

Is there a way to do a column formula so it all automates?

Jason Albrecht

Hi @Caleb W,

Great questions with challenging limitations.

You'll notice here some of the restrictions when using column formulas, including the inability to use @row references - which is what we'd need to use when each task is entered at the top of the sheet.

Would you be willing to go into the Form Settings and change the New Submission to appear on the Bottom of the sheet? That said, it would still be a manual process.

For automation, we may need to enlist a helper list (likely in a different sheet that is a direct copy of the columns in question) in order to create a column formula.

I'll need some time between jobs to work this out (e.g., a helper sheet or column) and/or we could call on the Smartsheet brains trust community, such as @Paul Newcome to help us out.

Paul Newcome

My suggestion is to insert an auto-number column with no special formatting. We will then need a [WAF # / CO # Helper] column (text/number) with the following column formula:

=VALUE([WAF # / CO #]@row)

The formula to generate your unique IDs is going to vary depending on...

I notice that some are zero filled to 3 digits, some are zero filled to 2 digits, and some are not zero filled at all. Is there logic behind that?

Caleb W

@Paul Newcome,

The zeros in front of the number are due to several different team members doing the number entry through the form. That is one reason why I am looking to auto number as they come in. That would eliminate the irregular number entries, but also keep the numbers in order per job and per type.

Also due to the amount of indexcollect formulas and data shuttling deeper in the sheet to calculate wage rates, equipment rates, etc, I have completed/approved rows move to a separate sheet so nothing changes when other rates change.

Paul Newcome

So the [WAF # / CO #] column is where you want the formula to go then?

You also made mention that you already have 68 on your source sheet (where the formula goes). Do you have 1 - 67 on the destination sheet (the one that receives the move row automation?

Caleb W

I do not have number that high on the destination sheet as we are only a month or so into implementing Smartsteets. Some project are 8 months old that we rolled into this process at the number to date.

Paul Newcome

Hmm... Do you have at least the number 68 somewhere already?

Caleb W

Just in the main source sheet.

Tim Hanson

I just want to chime in here. I've been following this thread because @Caleb W was having almost the exact same issue as I was. I put in @Jason Albrecht's formula and I worked perfectly. I've updated my forms to populate at the bottom of the sheet, and when a new entry is added, it is automatically carrying the cell formula down to the new row. (Which is awesome!)

Also, I added an "CO# Override" column, and added an IF function to Jason's. So If for any reason a PM needs to modify a CO#, they can manually type it and replace the formulated CO#.

Thanks, all for you insight.

Caleb W

@Tim Hanson what does your full formula look like eiht the if statement? I would need that to get my number right for the existing data.

Tim Hanson

@Caleb W I added a column called "Auto # Override", then modified Jason's formula a bit to read like this:

=IF([Auto # Override]@row = "", (COUNT(COLLECT([# Value]$1:[# Value]4, [Type:]$1:[Type:]4, [Type:]@row, [Project ID:]$1:[Project ID:]4, [Project ID:]@row)) + 1)), [Auto # Override]@row)

My sheet is a little bit different from yours, but functionally the "# Value" column is my CO number, "Type" column is a dropdown that where the user inputs if the row is a change order, RFI, or other submittal, and the "Project ID" column is a dropdown where the user inputs what project this row is for.

So this function is automatically assigning the next change order number, RFI number, or other submittal number, per project... and the override column makes it a manual entry again.

Tim Hanson

I don't know if this screenshot really helps, but here is the formula as I currently have it. I also added some cell references and text into the Auto # formula to make the cell report the project code, submittal type, and our company name at the end. We use this for our file naming conventions and this was a pretty smooth way to have it display all that information on a page when we generate the coversheet.