I want to lock a formulae for auto-increment

Saad

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

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)

Saad

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.

Saad

With respective to the above post

please note that Sheet1 ID has

r1

r2

r3

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

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?