Sum and subtraction

Iron Contributor

Dec 06, 2020

Ramsousa1986

Got it - looks like the nested IF function will be the best route.

Part 1: Formulas

Your formula(s) would be broken into three parts since you have three columns, which you can see in the example below (by each column):

*you need to adjust the formula for the first case on your end

Column 1 ' Subtracting total from other integer

=IF(AND(F1<>"",G1<>""),G1-F1,"")

Column 2 ' Subtracting total from other integer

=IF(AND(G1<>"",E1<>""),G1-E1,"")

Column 3 ' Adding both integers

=IF(AND(E1<>"",F1<>""),E1+F1,"")

Part 2: Enable iterative calculation

To not get constant circular reference popups, you have to enable type of calculation in the Excel Settings.

Go to File > Options > Formulas > Enable iterative calculation

Ramsousa1986
Copper Contributor
Dec 08, 2020
HansVogelaar
adversi
Twifoo

Thank you for your help
for now I will give up this idea

Bye

HansVogelaar

MVP

Dec 06, 2020

Ramsousa1986

I don't think this is going to work, but you could try the following:

Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code shown below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.
You'll find that there are situations in which the code can't decide what to do, so the sum will not be correct.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row < 17 Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo ExitHere
    Select Case Target.Column
        Case 3
            If Target.Value = "" Then
                Target.Offset(0, 3).Value = Val(Target.Offset(0, 2).Value)
            ElseIf Target.Offset(0, 2).Value = "" Then
                If Target.Offset(0, 3).Value <> "" Then
                    Target.Offset(0, 2).Value = Target.Offset(0, 3).Value - Target.Value
                End If
            Else
                Target.Offset(0, 3).Value = Target.Value + Target.Offset(0, 2).Value
            End If
        Case 5
            If Target.Value = "" Then
                Target.Offset(0, 1).Value = Val(Target.Offset(0, -2).Value)
            ElseIf Target.Offset(0, -2).Value = "" Then
                If Target.Offset(0, 1).Value <> "" Then
                    Target.Offset(0, -2).Value = Target.Offset(0, 1).Value - Target.Value
                End If
            Else
                Target.Offset(0, 1).Value = Target.Offset(0, -2).Value + Target.Value
            End If
        Case 6
            If Target.Offset(0, -3).Value = "" Then
                If Target.Offset(0, -1).Value <> "" Then
                    Target.Offset(0, -3).Value = Target.Value - Target.Offset(0, -1).Value
                End If
            ElseIf Target.Offset(0, -1).Value = "" Then
                Target.Offset(0, -1).Value = Target.Value - Target.Offset(0, -3).Value
            End If
    End Select
ExitHere:
    Application.EnableEvents = True
End Sub

adversi
Iron Contributor
Dec 06, 2020
Ramsousa1986
I don't think VBA is necessary - I'm not sure how it would work, that's better to ask for someone is more familiar.
Ramsousa1986
Copper Contributor
Dec 06, 2020
adversi

I gess i need to use VBA
adversi
Iron Contributor
Dec 06, 2020
Ramsousa1986
The point is to lose the formula. It is not possible to retain the formula and input your figure at the same time.

You drag down the formulas (before replacing with actual data) down to the last row you will need it for and input 2/3 numbers.

The remaining formula will calculate based on what is available, making the calculation dynamic.

Forum Discussion