asked on

Formula Calculation gone wrong

Hi,

I have a spreadsheet with the following tabs:

Main Sheet : Open : Closed : Cancelled

I have code that when I change the Status Column, it will move between the following tabs based on the status - Open : Closed : Cancelled

This seems to be working fine.

The problem I have is on my Main Sheet. The calculations of movement are not reporting correctly, have a look at items closed for March 2013 as an example.

Is there a simpler way to do these calculations, specially when I have to extend the months and want to hide some earlier months but still want to see the totals being displayed correctly

Hope this makes sense

Eddie
test.xlsm

Martin Liss

I may be missing something don't see any formula or code that would update the Open : Closed : Cancelled sheets.

propolis

ASKER

Hi,

There is some code in the: Developer --> Editor --> This Workbook

Is this what you want to see

Eddie

Martin Liss

Okay I saw that code and didn't realize what I was looking at. Let me look at it now in more detail and I'll get back to you.

propolis

ASKER

Thank you

Martin Liss

I hope you don't mind but I changed some of the variable names in the code to make it easier for me to understand. I also added some validations. Neither change will fix your problem and since I don't understand how the data is updated on "Main Sheet" I probably can't help any further. Can you explain?

Option Explicit ' New
Private Sub Workbook_SheetChange(ByVal wsSource As Object, ByVal Target As Range)

    Dim wsTarget As Worksheet
    Dim J As Long
    Dim LastIssueNo As Long
    Dim lngLastSourceRow As Long
    
    lngLastSourceRow = wsSource.Range("I65536").End(xlUp).Row
    If Target.Row < lngLastSourceRow Then
        Exit Sub
    End If
    
    Select Case wsSource.Name
        Case "Open", "Closed", "Cancelled"
            If Target.Column = 9 Then
                Set wsTarget = Sheets(Target.Value)
                
                J = wsTarget.Range("A:A").End(xlDown).Row

                
                If J = 1048576 Then
                    J = 1
                    LastIssueNo = 0
                Else
                    LastIssueNo = wsTarget.Cells(J, 1)
                End If
                       
                Rows(Target.Row).Copy Destination:=wsTarget.Rows(J + 1)
                               
                ' If you would like to add 1 to the last issue# in the target sheet, uncomment this code
                
        '        If J > 1 Then
        '            wsTarget.Cells(J + 1, 1) = LastIssueNo + 1
        '        Else
        '            wsTarget.Cells(J + 1, 1) = 1
        '        End If
                
                Rows(Target.Row).Delete
                
                wsTarget.UsedRange.Sort Key1:=wsTarget.Range("A2"), Order1:=xlAscending, Header:=xlYes
                
            End If
    End Select
    
End Sub

Open in new window

byundt

Eddie,
The formulas in the Main worksheet look good to me. Did you perhaps set the calculation mode to Manual? If so, they won't update. Change it back to Automatic.

Your code is called recursively as you transfer rows of data from one worksheet to the other. This can be avoided by having events turned off while the macro runs. I modified the code accordingly:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim Sh1 As Worksheet

    Select Case Sh.Name
    Case "Main"     'Do nothing with this worksheet
    Case Else
        If Target.Column = 9 Then
        
            Set Sh1 = Sheets(Target.Value)
            
            J = Sh1.Range("A:A").End(xlDown).Row
            
            If J = 1048576 Then
                J = 1
                LastIssueNo = 0
            Else
                LastIssueNo = Sh1.Cells(J, 1)
            End If
                   
            Application.EnableEvents = False
            Rows(Target.Row).Copy Destination:=Sh1.Rows(J + 1)
                           
            ' If you would like to add 1 to the last issue# in the target sheet, uncomment this code
            
    '        If J > 1 Then
    '            Sh1.Cells(J + 1, 1) = LastIssueNo + 1
    '        Else
    '            Sh1.Cells(J + 1, 1) = 1
    '        End If
            
            Rows(Target.Row).Delete
            
            Sh1.UsedRange.Sort Key1:=Sh1.Range("A2"), Order1:=xlAscending, Header:=xlYes
            Application.EnableEvents = True
            
        End If
    End Select
    
End Sub

Open in new window

propolis

ASKER

Hi,

If you look at the 'Closed' sheet and turn on filtering for column H, select Jan 2013 and it shows 2 records. March shows 12 records and May shows 4 records

Now go to the 'Main' Sheet. Look at the Table:

Jan-2013 shows 10 rows 'Closed' when it should be 2.

March-2013 shows 1 row 'Closed' when it should be 12.

May-2013 shows 1 row 'Closed' when it should be 4.

Now look at the graph:

Jan-2013 shows 2

March-2013 shows 10

May-2013 shows 1

Cells C9 and C10 on the 'Main Sheet' is correct in terms of numbers as per the numbers on the 'Open' and 'Closed' sheets. What is not correct is the number of movements for each month being displayed.

Hope this helps

Eddie

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

propolis

ASKER

Hi,

On the Main Sheet the 'Open Row' should look at the 'Log Date' and the 'Closed Row' should look at 'Close Date'

What do I need to change? Just the ref in closed to !H:H and the same for the cancel formula, looks at the closed date, so also looking at !H:H

Eddie

propolis

ASKER

OK,

That seems to work now.

Thanks for pointing that out

Eddie

propolis

ASKER

Thanks for pointing out this pointing error