Help with a formula to auto number

Meredith Rhodes

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?

Thank you for your ideas.

Meredith

jmyzk_cloudsmart_jp

Hi @Meredith Rhodes

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.

https://app.smartsheet.com/b/publish?EQBCT=d5d174f880104a02ad5746bc0afd0aa1

jmyzk_cloudsmart_jp

Hi @Meredith Rhodes

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.

https://app.smartsheet.com/b/publish?EQBCT=d5d174f880104a02ad5746bc0afd0aa1

Meredith Rhodes

@jmyzk_cloudsmart_jp You are incredible :)

Thank you so much for this!

Cheers!

Meredith

jmyzk_cloudsmart_jp

Happy to help.