Forum Discussion

jrwardesi's avatar
jrwardesi
Copper Contributor
May 10, 2021
Solved

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...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    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.

mathetes's avatar
mathetes
Gold Contributor
May 10, 2021

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."

Resources