How to make a manual Row ID based on conditions?
I'm not even sure if this is possible but I invite all the formula wizards to take a crack at it!
I have this intake sheet with 10 rows, all that have a corresponding auto-generated Row ID from the system column. Row 1 is 23-001, row 2 is 23-002, row 3 is 23-003, etc. When a project gets approved, I want another column, lets call it 'Salutation', to pull that Row ID. Otherwise, the 'Salutation' column will be left blank. For example:
Row 1 gets approved. Row 2 gets denied. Row 3 gets approved. So the 'Salutation' for 1 will get triggered to pull the Row ID and it will show "23-001". But then, I want row 3 to pull what Row 2 would have gotten if it was approved. Meaning, I don't want row 3's 'Salutation' to be "23-003". I want it to be "23-002". I always want the 'Salutation' column to grab what's next in line if that makes sense.
Is there a formula that can achieve this?
Best Answer
-
Genevieve P. Employee Admin
Here's an example formula that may be able to help you:
=IF(Status@row <> "Approved", "", INDEX([Row ID]:[Row ID], COUNTIF(Status$1:Status@row, "Approved")))
This first says, if the Status isnotapproved, then return a blank cell.
=IF(Status@row <> "Approved", "",
Otherwise, use the INDEX function to look into the Row ID column and bring back the row on the list that's the same number of "Approved" rows there are, specific to this current row.
Notice that the COUNTIF function references the top row cell: Status$1 and then the range goes down to the current row cell: Status@row
Because of this, you won't be able to turn it into a column formula. However you can drag-fill this down the column and then all new rows will auto-populate with the formula as long as new rows are added immediately under current rows. See:Use or Override Automatic Formatting and Formula Autofill
Cheers,
Genevieve
Answers
-
Genevieve P. Employee Admin
Here's an example formula that may be able to help you:
=IF(Status@row <> "Approved", "", INDEX([Row ID]:[Row ID], COUNTIF(Status$1:Status@row, "Approved")))
This first says, if the Status isnotapproved, then return a blank cell.
=IF(Status@row <> "Approved", "",
Otherwise, use the INDEX function to look into the Row ID column and bring back the row on the list that's the same number of "Approved" rows there are, specific to this current row.
Notice that the COUNTIF function references the top row cell: Status$1 and then the range goes down to the current row cell: Status@row
Because of this, you won't be able to turn it into a column formula. However you can drag-fill this down the column and then all new rows will auto-populate with the formula as long as new rows are added immediately under current rows. See:Use or Override Automatic Formatting and Formula Autofill
Cheers,
Genevieve
-
Gabriel Barrera ✭✭✭
I'll try it@Genevieve P.! Thank you!
-
Gabriel Barrera ✭✭✭
@Genevieve P.that worked, thank you so much! That's a useful trick to keep in mind.
-
Genevieve P. Employee Admin
Awesome! Glad I could help
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/379877/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"