They won't let me add new sheets or columns. To be fair to them, this
spreadsheet has grown out of all control (it's over 50Mb now) and has been
messed with by a lot of people.Because of this, there are strict controls on
what you can do to it and you need strong justifications if you're to add
new sheets or columns.

Thanks
Steve


"Arvi Laanemets" wrote in message
...
Hi

Not exactly what you described, but near enough:

You have a sheet with column ID, where you need unique identificators for
every row.

Add a sheet ID, with columns FreeNr, Nr, Used, ID (headings in row 1)
ID!A2=IF($C2=TRUE,"",COUNTIF($C$2:$C2,FALSE))
ID!B2=ROW()-1
ID!C2=COUNTIF($B2,Sheet1!$A:$A)0
ID!D2=IF(ISERROR(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),)),"",TEXT(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),),"00000"))
Copy cells A2:D2 down for some reasonable amount of rows (you can expand
this table later, whenever you run out of free ID's)

Define a named range
ID=OFFSET(ID!$D$1,1,,COUNTIF(ID!$D:$D,""&"""")-1,1)

On your working sheet, select a range in ID column, and apply Data
ValidationList with source =ID

Whenever you add an entry, you are allowed only to enter (manually or from
dropdown) unused ID's .
NB! You can copy a non-unique value into ID column although (p.e. when
coping rows). You can use conditional formatting to indicate such
non-unicue ID values, p.e. through different font color - so you can
change them.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Steve Barnett" wrote in message
...
I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
need to be able to identify each row via a unique identifier, but am
having trouble working out what to do when new rows are added or when
existing rows are copied and paste.

New rows and copied rows should get new unique identifiers, but I can see
no way of doing this. Does anyone have any suggestions? There is nothing
unique about the data that I can hang on to and the "SheetChanged" event
does not fire for inserted rows in Excel 2000.

This spreadsheet is owned by one of our clients, so I can't add new
worksheets or columns to the existing workbook - I can just add cell
comments (unless you have a better suggestion).

Can anyone help?

Thanks
Steve