Function Problems

Excel Formula to Generate Next Sequential Project Number by Year

Rana Garcia Oct 9, 2026 870 views

Question details

The user wants to create a dynamic formula to automatically generate sequential project numbers structured with a three-digit sequence and a four-digit year (e.g., 001-2023).

How to Generate the Next Project Number by Year Using an Excel Formula

Product: Excel
Device & OS: not provided
Scenario: Managing project trackers or logs where automatic, year-based sequential numbering is needed to ensure accurate tracking without manual data entry.
Observed behavior: The user needs an automated way to generate sequential IDs so that the numbering increments correctly based on the previous entry and automatically resets to 001 when a new calendar year begins.

Before you start

Ensure your project tracker has a dedicated column for project numbers, and that you manually enter the very first project number (e.g., 001-2023) so the formula has a reference point to read and increment.

Solution 1Recommended

Use the LET Function to Dynamically Extract and Increment the Number

This solution uses a combination of advanced formula functions (LET, LOOKUP, TEXT, CONCAT) to find the last entered project number, check the current year, and automatically generate the next sequence in the proper format.

The LET function allows us to define variables within the formula to make it cleaner and more efficient. By isolating the last sequence number and the current year, the formula can automatically detect if the year has changed and increment the number accordingly.

Locate the target cell

Select the empty cell in your project number column where you want the new sequential project number to appear.

Enter the sequence formula

Type or paste the following formula into the formula bar: `=LET(last,LOOKUP(2,1/(A:A<>""),A:A),seq,LEFT(last,FIND("-",last)-1),y,RIGHT(last,4),yy,TEXT(TODAY(),"yyyy"),newseq,IF(y=yy,TEXT(VALUE(seq)+1,"000"),"001"),CONCAT(newseq,"-",yy))`.

Adjust the column reference

The formula assumes your project numbers are in column A. If they are in a different column (e.g., column B), change all `A:A` references in the formula to `B:B`.

Apply the formula

Press Enter to generate the next project number. You can then drag the fill handle down to apply this logic to subsequent blank rows.

Use the LET Function to Dynamically Extract and Increment the Number

Compatibility Note: The LET function is available in newer versions of Excel and WPS Spreadsheet. If you are using an older version, the formula will return a #NAME? error and you may need to use a standard combination of IF and LOOKUP formulas instead.

Automate Data Effortlessly

Streamline Project Tracking with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like LET and LOOKUP, making it incredibly simple to automate project numbers, track tasks, and manage data seamlessly without tedious manual data entry.

1. Open your project tracker: Launch WPS Spreadsheet and open the workbook where you log your projects.
2. Enter the initial number: Manually type your first project number (e.g., 001-2024) into the first cell of your tracking column.
3. Apply the dynamic formula: Paste the provided LET formula into the next blank cell in your project number column.
4. Drag to fill: Click the small square at the bottom right of the cell and drag the fill handle down to automatically generate sequential numbers as you add new projects.

Fully compatible with Microsoft Excel formulas, .xlsx files, and conditional formattingBuilt-in advanced dynamic array formulas for complex data automationLightweight and runs smoothly across Windows, Mac, Linux, and mobile devices100% free alternative for powerful daily data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return an #N/A or #VALUE! error?

This typically occurs if the referenced column is completely empty, or if the previous entry does not follow the correct format with a hyphen (e.g., 001-2023). Ensure your first project number is manually entered in the exact format before using the formula.

How can I change the formula to generate a two-digit sequence instead of three digits?

To use a two-digit sequence (like 01-2023), modify the IF statement at the end of the formula: change `"000"` to `"00"` and `"001"` to `"01"`.

Will the sequence number automatically reset when a new calendar year starts?

Yes. The formula uses the `TODAY()` function to dynamically check the current year. If the current year does not match the year extracted from the last entered project number, the sequence automatically restarts at 001.

Excel & Data Problems