lesley burlingham
Member
I would like a unique reference inserting onto cell E24. I need it to contain data form cell B7 and then a random set of numbers and letter. (5 random letter/numbers)
Generate a unique Ref
- Thread starter lesley burlingham
- Start date
NARAYANK991
Excel Ninja
Hi ,
I am sure this is not your complete requirement.
For starters , how many cells will have this formula ? Is it just one cell ?
Narayan
I am sure this is not your complete requirement.
For starters , how many cells will have this formula ? Is it just one cell ?
Narayan
lesley burlingham
Member
Yes I only want the unique reference in E24. The sheet does have other fields but these are not required for the reference number, just B7.
NARAYANK991
Excel Ninja
Hi ,
If you want something in just one cell , then anything that you choose will be unique. Or am I missing something ?
Narayan
If you want something in just one cell , then anything that you choose will be unique. Or am I missing something ?
Narayan
Chihiro
Excel Ninja
I'm guessing that you need this for tracking elsewhere, to be used as unique identifier for the specific form.
In those instances, instead of making it a random numbers I'd suggest logical numbering system. For an example: 5 letter code for client/agent/etc, followed by year, week#, date, & incremental number (if there's more than 1 for same client/agent/etc on same date).
However, in order for us to suggest code, we need full scope. Does unique ID only have to be unique on the sheet itself or does it need to be checked on another sheet? Is it being referenced else where and how is it used? Does it need to be sequential? So on and so forth.
In those instances, instead of making it a random numbers I'd suggest logical numbering system. For an example: 5 letter code for client/agent/etc, followed by year, week#, date, & incremental number (if there's more than 1 for same client/agent/etc on same date).
However, in order for us to suggest code, we need full scope. Does unique ID only have to be unique on the sheet itself or does it need to be checked on another sheet? Is it being referenced else where and how is it used? Does it need to be sequential? So on and so forth.
lesley burlingham
Member
Hi
Thank you for getting back to me. The unique code is onl;y used on one cell. It is not referenced anywhere else. It is just n identifier for our record.
Your suggestion of a 5 letter code for client/agent/etc, followed by year, week#, date, & incremental number.
The client code is input into cell B7. We could then add Year, Week & incremental number. So it would look like (65691547587) (6569,15,47,587).
is this possible
Thank you for getting back to me. The unique code is onl;y used on one cell. It is not referenced anywhere else. It is just n identifier for our record.
Your suggestion of a 5 letter code for client/agent/etc, followed by year, week#, date, & incremental number.
The client code is input into cell B7. We could then add Year, Week & incremental number. So it would look like (65691547587) (6569,15,47,587).
is this possible
Chihiro
Excel Ninja
Sorry about the late reply. I missed your post.
For sequential or incremental number, you need reference point. Since this is only used in single cell and not being used elsewhere... there really is no point. So instead you can use random number.
Code will look like below. However, you may want to change "Date" portion. I just used it as place holder. If you have some dates tracked on your sheet, you may want to use that instead.
Also, don't forget to set format of E24 to Text. Otherwise, you will get scientific number.
For sequential or incremental number, you need reference point. Since this is only used in single cell and not being used elsewhere... there really is no point. So instead you can use random number.
Code will look like below. However, you may want to change "Date" portion. I just used it as place holder. If you have some dates tracked on your sheet, you may want to use that instead.
Also, don't forget to set format of E24 to Text. Otherwise, you will get scientific number.
Code:
Sub uniqueIndex()
Dim cCode As String
Dim yCode As Integer
Dim wNum As Integer
Dim rNum As Long
Dim uCode As String
cCode = ThisWorkbook.Sheets("Sheet1").Range("B7")
yCode = Right(Year(Date), 2)
wNum = Format(Date, "ww")
rNum = Int((99999 - 1 + 1) * Rnd + 1)
uCode = cCode & yCode & wNum & rNum
ThisWorkbook.Sheets("Sheet1").Range("E24") = uCode
End Sub