Search code examples
excel-formula

I am trying to create a formula in Excel that will generate a unique number if a reference cell is not blank, but the number be 1 more than the last

The formula I tried, but doesn't work quite right is:

=IF([@[DMR?]]<>"",2025 & MAX($F1:F1)+1,"")

The idea is to create a unique number that is 1 more than the last number created. I am trying to embed the year to help know what excel database to find information on the unique number as we archive the spreadsheet every year and start fresh. The formula does generate a number, but it is always giving me "20251" regardless of the last time the formula resulted in "True".

Is what I am trying to do possible with just a formula? Thanks in advance!

Solution 
  • =LET(x_,TEXTAFTER(""&C$2:C2,"2025",,,,""),y_,IF([@DMR]<>"",--(2025&(MAX(IFERROR(--x_,0))+1)),""),y_)

    unique fn

    requires Office 365 comp. v. Excel

    for 'neatly' ordered refs, try something like this instead:

    =LET(x_,TEXTAFTER(""&C$2:C2,"2025",,,,""),y_,IF([@DMR]<>"",--(2025&(TEXT(MAX(IFERROR(--x_,0))+1,REPT("0",LEN(""&ROWS([DMR])))))),""),y_)

    ordered fn