Unique Sequential Numbers

[Andy42k]

Hi all. I am trying to create a spreadsheet to keep track of different units that submit requests to my unit throughout the year with a unique sequential number. The units are designated by the numbers 1-22. I would like the sequential number to read as follows: 15 - 1 -23...the number 15 would the number of the unit making the request, the number 1 would the number request for the year, and 23 would the year. Any direction with this would be greatly appreciated. TIA

 

[etaf]

how is the unit determined , is this entered on the sheet
otherwise you could put
'15-1-23 into cell A2
and then
="15-"&MID(A2,4,1)+1&"-"&TEXT(TODAY(),"yy")
copied down

perhaps with an IF - so a cell has to be completed in that row for it to show up

Or you could use ROW()
="15-"&ROW()-1&"-"&TEXT(TODAY(),"yy")

Book1
	A	B
1
2	15-1-23	15-1-23
3	15-2-23	15-2-23
4	15-3-23	15-3-23
5	15-4-23	15-4-23
6	15-5-23	15-5-23
7	15-6-23	15-6-23
8	15-7-23	15-7-23
9	15-8-23	15-8-23
10	15-9-23	15-9-23
11	15-10-23	15-10-23
12	15-2-23	15-11-23
13	15-3-23	15-12-23
14		15-13-23
Sheet1

Cell Formulas
Range	Formula
A3:A13	A3	="15-"&MID(A2,4,1)+1&"-"&TEXT(TODAY(),"yy")
B2:B14	B2	="15-"&ROW()-1&"-"&TEXT(TODAY(),"yy")

 

[Andy42k]

Thank you for your response. The number of the unit will be inputted on the sheet.

 

[etaf]

then you could just use concatenate to add the unit number
but presumably you would want

15-1-23
15-2-23
18-1-23
15-3-23
18-2-23



and not
15-1-23
15-2-23
18-3-23
15-4-23
18-5-23

maybe an example with expected results would help
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Andy42k]

The unit numbers would be picked from a list in the cell. I need a number sequential number generated a particular number is picked from row 1.

 

[etaf]

sorry for the delay, i was getting into all sorts of very complicated formulas using max and search

BUT a count may do

how about

Book30
	A	B	C	D	E	F	G
1	unit						ID
2	1						1-1-23
3	3						3-1-23
4	5						5-1-23
5	1						1-2-23
6	3						3-2-23
7	6						6-1-23
8	1						1-3-23
9	3						3-3-23
10	5						5-2-23
11	1						1-4-23
12	3						3-4-23
13	6						6-2-23
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=A2&"-"&COUNTIF($A$2:A2,A2)&"-"&TEXT(TODAY(),"yy")