How to use an IF statement with multiple cells and conditions?

Question

I need some assistance with an IF statement/formula.

If G24 is greater than zero, subtract G3 from G24 and display the results in G3 If G24 is null/blank, then keep G3's current value

Here is what I have so far but I know this needs to change.

=IF(G24>0,G3-G24,G3)

Example: Someone works a 40 hour work week so 40 hours is entered in G3. If a PTO number is entered in G24, for example "8" - I would like G3 to subtract G24 from it's value (40-8) = 32. If there are no PTO hours entered in G24, I would like for the current 40 hours in G3 to stay the same.

JB-007 · Answer

Sheet1 with VB code below does the following:

Enter value in G3 (sheet1) and it clears G24, and stores this in hidden sheet (so calc can be repeated for different values of G24 and same G3)
Only when G3 selected and changed manually will the hidden sheet be updated to the value you enter
Enter value in G24 and calc per Question is peformed against original value entered into G3 as described above

Notes: To insert this macro, make sure workbook is macro-enabled, save as .xlsb or .xlsm), and see this (internet-archived/persistent) link.

Macros (Sheet 1 module - comprises 2 subs and 1 function)

Public sheet_name As String


Private Sub Worksheet_Change(ByVal Target As Range)
'https://www.teachexcel.com/excel-tutorial/run-a-macro-when-a-specific-cell-changes-in-excel_1592.html

    sheet_name = "hidden_backup"
    

    If Target.Address = "$G$24" Then
        If ActiveSheet.Range("g24").Value > 0 Then
            If SheetCheck(sheet_name) = True Then
                Range("g3").Value = Sheets(sheet_name).Range("a1").Value - Range("g24").Value
            Else
                MsgBox "populate cell g3 first"
                ActiveSheet.Range("g24").Value = ""
            End If
        End If
    
    End If
    
    
    On Error GoTo err_trap
    If (Target.Address = "$G$3") Then
        'Set cell = ActiveSheet.Selection
        If Not Intersect(Range("g3:g4"), ActiveCell) 
    Then
            Call update_hidden
            Range("g24").Value = ""
        End If
    End If
    
    err_trap: Exit Sub

    
    End Sub

Function SheetCheck(sheet_name) As Boolean
'https://excelchamps.com/vba/add-new-sheet/
'Dim ws As Worksheet

SheetCheck = False
 
For Each ws In ThisWorkbook.Worksheets
 
    If ws.Name = sheet_name Then
    
        SheetCheck = True
        
    End If
 
Next
 
End Function

Sub update_hidden()
sheet_name = "hidden_backup"
If SheetCheck(sheet_name) = False Then
    
    Worksheets.Add().Name = sheet_name
    
End If

Sheets(sheet_name).Range("a1").Value = ActiveSheet.Range("G3").Value
Sheets(sheet_name).Visible = False

End Sub

Screenshots

Scenario/illustration:

Step 1: populate g3

Step 2: populate g24 (calc updates g3)

Step 3: update/change g24 (calc repeated using original g3 value)

Step 4: user updates g3 directly and starts over (1-3)

How to use an IF statement with multiple cells and conditions?

1 Answers