How to formulate a cell to get the date and info from other cells for creating a progressive number

[farzadtb]

Hi All

I would like to create a formula that generates a number based on date and other cells which will be composed of 3 parts :

1901- 50158-0001

So the first part is the year and month : like if it's 2019 january, it will make 1901, the second is a combination of values from multiple cells , like if cell A11 = 50, A12 = 15 and A13 = 8 , then the last part is only progressive number . this code will be create on a column ,so the successive cell in the column will be a progression of the previous therefore that 0001 will change to 0002 and so on. At the moment no idea comes to my mind how to do this.

 

[Gerald Higgins]

Does this number need to be dynamic ?
For example, when the month changes to February, should the code change to
1902-50158-0001
?

For example, if the value in A11 changes to 100,000, should the code change to
1901-100000158-0001
?

 

[farzadtb]

Dear Gerald, that is absolutely right. All of the points

 

[farzadtb]

Is it possible only by Formula?

 

[onlyadrafter]

Hello,

Have assumed the last part of the number is defined by the column, so Col A is 0001 then Col B is 0002

=RIGHT(YEAR(NOW()),2)&TEXT(MONTH(NOW()),"00")&"-"&A12&A13&A14&"-"&TEXT(COLUMN(),"0000")

Does this work as expected?

 

[farzadtb]

Dear Gerard, I copied this and chose the right cells , but Excel did not recognized the formula and bugged me with the error message and jumps to the 2 which is after YEAR(NOW()). by the way the last part of the number is defined by row not colomn. so I only change the column() to row() ?

 

[byundt]

You may simplify the date reference by using a single TEXT with the proper format code. You may also include the hyphens in the TEXT. Adding $ to fix the row number references for A11:A13, I get:
=TEXT(TODAY(),"yymm-")&A$11&A$12&A$13&TEXT(ROWS(A$1:A1),"-0000")

 

[farzadtb]

I thank you again for the help, I started using this code last month, and made like 10 codes. but then when the month changed, even the old codes with 1902 changed to 1903. is there a solution to this ?

 

[byundt]

I thank you again for the help, I started using this code last month, and made like 10 codes. but then when the month changed, even the old codes with 1902 changed to 1903. is there a solution to this ?

Sorry, but that's how formulas work--they update.

There are a few alternatives:
1. Before the month changes, copy the cells with the formula, then Paste Special...Values.
2 Change the TODAY() part of the formula to reference a cell any date in the desired month & year. So you might put February 1, 2019 in cell Z2. Your formula would then be:
=TEXT($Z$2,"yymm-")&A$11&A$12&A$13&TEXT(ROWS(A$1:A1),"-0000")
Then in March, you would put March 1, 2019 in cell Z3 and change the formula to:
=TEXT($Z$3,"yymm-")&A$11&A$12&A$13&TEXT(ROWS(A$1:A1),"-0000")
3. Use a macro to build the code, say when you doubleclick the cell. The code below should go in the code pane of the worksheet being watched. There are two statements, each with comments, that you must change to suit your worksheet layout.

<code>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Put a serial value in the cell that is doubleclicked using today's date and the values in cells A11:A13
'If cell already has a value, it will not be updated!
Dim cel As Range, targ As Range
Dim start As Long
Set targ = Me.Range("D2:D100") 'Watch these cells for doubleclick and insert a code
start = 2 'Row number of first cell with code
Set targ = Intersect(targ, Target)

If Not targ Is Nothing Then
For Each cel In targ.Cells
If cel.Value = "" Then
cel.Formula = "=TEXT(TODAY(),""yymm-"")&A$11&A$12&A$13&TEXT(ROW() -" & start & "+1,""-0000"")"
'=TEXT(TODAY(),"yymm-")&A$11&A$12&A$13&TEXT(ROWS(A$1:A1),"-0000")
cel.Formula = cel.Value
End If
Next
Cancel = True
End If
End Sub</code>