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.

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

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

3 Replies

HansVogelaar

MVP

May 10, 2021

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.

jrwardesi
Copper Contributor
May 10, 2021
Thank you so much Hans for the quick reply and the fix. Works perfectly!

mathetes
Gold Contributor
May 10, 2021
jrwardesi
Some questions for you.
What is the volume of jobs bid and won on any given day?
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."

Forum Discussion

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

3 Replies

Resources