Fixed row value using tables

[leemwright11185]

Hi Everyone,

I am trying to get my spreadsheet to allocate each row in my table with a unique 3 digit reference using =TEXT(ROW(A1),"000"). However I would like to be able to sort the data by date or location etc. without the number changing. I have the below test data but when I sort the column by date order the reference number stays in the same order as previous.

NO.	LOCATION	DATE
001	ABZ	05/10/2018
002	DUN	01/10/2018
003	GTY	18/10/2018
004	GTY	12/10/2018

<tbody>
</tbody>

What can I use so that the number field is fixed at point of new table line being created and it can be sorted etc? Any help appreciated.

Lee.

 

[Fluff]

Hi & welcome to MrExcel.
One option is to copy/paste the formula as values.
Another option is to use
=TEXT(ROW($A$1),"000")
But this means you wont be able to "fill down" the formula.

 

[leemwright11185]

Thanks for getting back to me fluff. Unfortunately I had already tried your suggestion. I'm trying to make my spreadsheet as "idiot" proof as possible as we have users here who are not great with computers so I don't trust them to copy and paste it properly.

Can you think of any other ways of automatically creating a unique reference without using the ROW formula perhaps?

TIA

 

[Fluff]

The only other way I can think of to have the numbers "hard coded" would be VBA.
That said I'm not too hot on formulae, so somebody else may have an idea.