Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    How to make a manual Row ID based on conditions?

    ✭✭✭✭
    edited 06/09/23 in Formulas and Functions

    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?

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Employee
      Answer ✓

      Hi @Gabriel Barrera

      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")))

      Screenshot 2023-06-09 at 14.50.26.png


      This first says, if the Status is not approved, 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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter

    Answers

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions