Year-auto increment number?

[Scuttlebutt95]

We have an Excel table we use to create work orders. The work order numbers are structured as Fiscal Year - incremental 3 digit number. For example,
2018-001
2018-002
2018-003
etc.

Right now the numbers are entered manually in a table column each time a new WO is created. I've had some problems with people accidentally skipping a whole series of numbers (I have no idea how). I'm wondering if there is a way to prevent this sort of error. Is it possible to create an auto number with the correct formatting? (there is a new sheet for each fiscal year so that number can be static. Or maybe some sort of Data Validation or Conditional Formatting strategy? Any advice is appreciated.

W are working in Excel 2016 on Windows 10

TIA

 

[Joe4]

Welcome to the Board!

What row number is the first entry going in?
Let's say it is row 2 (i.e., you have a header row).
Then you could use a formula like this:
="2018-" & TEXT(ROW()-1,"000")
and copy down.

 

[Scuttlebutt95]

Joe4 - that worked pretty well, but I have to drag it down to fill the next row. Any idea on how to force it to autofill each time a new row is created in the table?

 

[Joe4]

Is it really a "table"? If so, then I think the formulas should populate automatically.
If not, you can use VBA code to do that, if you can define the condition when the formula should be populated.

 

[kvsrinivasamurthy]

IN A2 then drag down

=IF(B2="","","2018-" & TEXT(ROW()-1,"000")