Report

How to generate a STATIC unique identifier for each Excel row

Ask a question Musicar - Last answered on Oct 6, 2017 at 06:41 PM by ac3mark
Hello,

How can generate a unique identifier for each Excel row that WILL NOT change when I delete or add rows?


Helpful
+0
plus moins
Well, right off the head, this can be accomplished with a two step method. Stay with me.

A column that holds the UNIQUE number, along with another column that keeps track of whether or not the first colum has a vlaue in it, and upon a value being placed in the first column, does not recalculate. Try to imagine:

Column A hold boolean (y/n)
Column B hold unique value.
Column C holds the actual data.

If column A is set to N (default), which in this case means "NOT LOCKED", then a unique number is generated!

Once a value is set for C, then A should be changed to Y, and no other unique number will be generated in B, unless you manually set A to "N".

Just one VERY simple way of locking the values in!


Leave a comment
Helpful
+0
plus moins
@ac3mark - I think this will work for the situation I am in as well. Would you mind giving an example of how to set this up (what would go in each column). Thanks!
ac3mark 8020Posts Monday June 3, 2013Registration date ModeratorStatus October 22, 2017 Last seen - Oct 5, 2017 at 10:41 PM
Well, hang tight, its almost Friday, and I do my best posting on Friday!
Reply
Leave a comment
Helpful
+0
plus moins
OK, here we go. Please understand, this solution will generate an error, specifically a "CIRCULAR CELL REFERENCE ERROR", but it does not change or alter the end result for locking the values.

Take a look at the image, it contains three seperate screen shots, each one after hitting F9 (recalculate). Notice the error at the bottom of the image, it was generated by the FIRST time the formula was triggered. This is a not so glam way of handling this.



Now, you could make a button to lock , and unlock the sheet, but only buttons will trigger it, as a formula cannot trigger a module!

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!