How to auto number each row and have it locked to that row

[jhkgbs]

Hi there,

I have a table in an excel sheet and I want it to automatically generate the next new row number for that table but also have it locked to that row.
I have used the ROW formula before but when I go to filter the table, i.e. grouping the table by locations, the numbers update, giving each row a new number so that the numbers are in order. I need the numbers to be locked for each row.

The formulas I've tried are:
=TEXT(ROW(A1),"000-000")
=ROW()-ROW(Table2[#Headers])

They work fine but are not locked to the row.

This table is going to have hundreds of rows.

Example of the changing number:
filter example.png

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[jhkgbs]

I've uploaded an image of what happens

For some reason, the site won't let me attach a workbook?

cant attach.png

[dosydos]

Do you have to use a Table for your data?
You could just make a regular excel sheet and number them. Then drag and drop it down to the next cell. This way when you filter there is no changing of the formulas.