# Help with a formula to auto number

Options

Hello!

I have a need to create / automate a few subcategories when a new row is added to a sheet. Here is what I'm looking at.

You can see in the 'Budget Action Requested' column there are Initial actions and Amendments. The Unique ID column next to it collects data about the study to assist in populating the row using cross sheet formulas.

You can see for the Unique ID 0051 - there are 5 entries, one is Initial, and four are amendments. What I would like to do is, in the Task Number column, create a formula that says, look at the Budget Action Requested, if it's 'Initial', then just put the Unique ID, if it's 'Amendment' then put the Unique ID but add a number, like 0051-1, 0051-2, 0051-3, 0051-4...

I can get the first part of the formula - to return the Unique ID:

IF([Budget Action Requested]@row = "Initial", [Unique ID]@row, " ")

Is it possible to make a formula to do what I'm asking?

Meredith

Tags:

• ✭✭✭✭✭✭
Options

You can use the RANKEQ function in such cases.

https://help.smartsheet.com/function/rankeq

To rank [Budget Action Requested] that has an "Amendment" value and [Unique ID] is 0051, I would use a formula like this.

• =IF([Budget Action Requested]@row = "Amendment", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Unique ID]:[Unique ID], VALUE([Unique ID]@row) = VALUE(@cell), [Budget Action Requested]:[Budget Action Requested], "Amendment"), 1))

First, you need a unique number, like Row ID.

Then, for the range, you can use the COLLECT function.

Please take a look at this published demo sheet.

• ✭✭✭✭✭✭
Options

You can use the RANKEQ function in such cases.

https://help.smartsheet.com/function/rankeq

To rank [Budget Action Requested] that has an "Amendment" value and [Unique ID] is 0051, I would use a formula like this.

• =IF([Budget Action Requested]@row = "Amendment", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Unique ID]:[Unique ID], VALUE([Unique ID]@row) = VALUE(@cell), [Budget Action Requested]:[Budget Action Requested], "Amendment"), 1))

First, you need a unique number, like Row ID.

Then, for the range, you can use the COLLECT function.

Please take a look at this published demo sheet.

• Options

@jmyzk_cloudsmart_jp You are incredible :)

Thank you so much for this!

Cheers!

Meredith

• ✭✭✭✭✭✭
Options

Happy to help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!