On fly (I can't access excel currently):

I'm assuming it is a Projects table where you are registering new projects?

When yes, then instead having 4 columns for project types, have a single one, where you register the project type (probably values like "MFTP";
Then create a dynamic name (e.g. nProjType) which returns project type in current active row;
Then create a dynamic name, which calculates MAX value of numeric part 1*(last 3 characters) of all project numbers starting with same current project type string as in current row (e.g. nProjNo). The easiest way to get this is to have in Projects table a hidden column, where numeric part of project number for current row is calculated for all rows with same project type as in active row;
Then add an hidden sheet with formula in some cell like =IIF(ProjType="","",ProjType & RIGHT("000" & ProjNo+1, 3)), and define this cell as name also (like lProj - I use prefics l as this name is used to define a list - which will contain a single value btw.). This name returns now next project number for active row in Projects table;
In projects table, define project number column fields as Data Validation List with value = lProj. As result, the only value you can enter or select for project number column will be the new project number on hidden sheet.

NB! With setup described above, when you select new project number for row with non-empty project number, then old one will be replaced with new one. You can avoid this, when in formula on hidden sheet you check for current project number, and set the value of lProj as empty string in case the current project number <> "". I.e. you define another dynamic name e.g. nProj, and the formula on hidden sheet will be like
=IIF(OR(ProjType="",Proj <>""),"",ProjType & RIGHT("000" & ProjNo+1, 3)).
When project number in current row is not empty, the data validation list will be empty, and you can't select it.