Help with a formula to auto number
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
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Best Answer
-
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.
Answers
-
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.
-
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!