May 10 2021 06:35 AM
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.
May 10 2021 07:10 AM
Some questions for you.
The reason I ask: depending on volume, you could do something else altogether. For example,
May 10 2021 07:12 AM
SolutionI 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.
May 10 2021 07:54 AM
May 10 2021 07:12 AM
SolutionI 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.