SOLVED

New Contributor

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

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.

3 Replies

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

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."
best response confirmed by allyreckerman (Microsoft)
Solution

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

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.

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

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