I want to lock a formulae for auto-increment

I have manually added these values on row column

Is there any way I can make this a column formulae?

Although the whole point of this is to get the ID from another sheet.

I tried and I just want to create a formulae that gets this ID from another sheet, whenever it is added

Please note that I have used reference sheet name as ID.

For Example:

= {ID}@row or ={ID}1

But I am unable to do that. Therefore, I have to create a column by name row and add values to it.

Then using index function I get values from another sheet within ID column


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To get the row number as a column formula, you would need to insert an auto-number column (formatting doesn't matter and then in the Row column use this:

    =MATCH([Auto-Number Column Name]@row, [Auto-Number Column name]:[Auto-Number Column Name], 0)

  • Thanks, Paul.

    This formulae returns the position only if it is present.

    but this is limited to the same sheet.

    Is there a way that I can cross sheet reference it

    What I originally want to do is to get the ID from another sheet.

    Sheet1 ID Sheet2 ID

    r1 =sheet1{ID@row} result wanted is r1

    r2 =sheet1{ID@row} result wanted is r2

    r3 =sheet1{ID@row} result wanted is r3

    Then, lock this Sheet2 ID column formulae. So that it gets updated whenever a value is added in Sheet1 ID.

  • With respective to the above post

    please note that Sheet1 ID has




    While Sheet2 ID

    =sheet1{ID@row} result wanted is r1

    =sheet1{ID@row} result wanted is r2

    =sheet1{ID@row} result wanted is r3

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Right. That replaces the manual entry of the number in the row column. If that is not what you wanted, then can you provide a screenshot with the end result manually entered to show what you are wanting?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!