unique id generator

[janakg]

School project:creating a booking system that will generate a unique id every time some one books a show. I have the formula to produce a unique id but every time I move onto the next cell the id changes so after I finished typing in a name and move onto typing in the email the id changes. My excel skills are very basic so anyone have a simple solution?

Formula to create id:=(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,99999)

Example of how it look below

ID: Formula in here
Name:
Email:
Select show:

[:) Sixthsense :)]

Rand() / RandBetween() both are volatile functions which change the result of the functions when any change that occurs in excel.

[sandy666]

You can play with LEFT, RIGHT, MID, BASE and LEN functions

example

	A	B	C
1	TS00017	Tom Johns	[email protected]
2
3	=UPPER(LEFT(B1,1)&RIGHT(B1,1))&BASE(LEN(C1),10,5)

Sheet: Unique ID

[davsth]

there is no guarantee the id will be unique as well, what makes it unique?` If some element of the time the booking was made was put in the ID ="A"&TEXT(NOW(),"YYMMDDHHMMSS")

again it is a volatile function so you have to copy it as values.

or you just keep incrementing a number by 1 each time a new one is added, this probably the easiest

The solution would be with VBA as it could automatically create the value as text, so it wouldnt change, however we need to see a sample of the spreadsheet to put some code in to accomplish that

[sandy666]

or you can play with this

Please Login or Register  to view this content.

or shorter

Please Login or Register  to view this content.

both with iterative calculation enabled

example

	A	B	C
1	TS-0075108999	Tom Johns	[email protected]
2	MD-0036251263	Mike Oldfield	[email protected]
3	IN-0046018454	Ian Anderson	[email protected]
4	MD-0003784439	Mark Wild	[email protected]
5	MD-0034594813	Mike Wald	[email protected]

Sheet: unique id

you are limited by your imagination only