Auto fill the next available Project # when a project is "won"

[jrwardesi]

I have the attached Quote register for projects.  If a project is "won" in column G, I want the job # to be filled in with the next sequential # in column F.  This formula was suggested:  =IF(G2="Won",SUM(MAX(235,F$1:F1),1),"") but it only works if the projects are won in row order from top to bottom.  Otherwise, project #'s are renumbered when the project above it is won.  

[#2]

@jrwardesi 

Some questions for you.

1. What is the volume of jobs bid and won on any given day?
2. Do the Job numbers have to be sequential? Aren't they basically identifiers, codes that can be used in other tables to refer to the job by something other than name?

The reason I ask: depending on volume, you could do something else altogether. For example,

• Make the job number the underlying date number associated with the date on the day the contract is "Won." For example today's date is 5/10/21; Excel's number for that date is 44326. So 44326 could be the Job # for any contract won on this date.
• If there are many jobs won on any given day, you could get that down to the decimal based on date and time.....
• And, of course, if the volume is small enough, easily managed, there's no actual need to automate the assigning of a number. I know it's fun to write a formula--I've been bitten by the bug too--but there are times when simpler is better even if it's "manual."

[#3 · Accepted solution]

@jrwardesi 

I think you'll need VBA for this.

Rigt-click the sheet tab and select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("G:G"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("G:G"), Target)
            If rng.Value = "Won" And rng.Offset(0, -1).Value = "" Then
                rng.Offset(0, -1).Value = Application.Max(Range("F:F")) + 1
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

You'll have to allow macros when you open the workbook.

[#4]

Thank you so much Hans for the quick reply and the fix. Works perfectly!