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
propolisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I may be missing something don't see any formula or code that would update the Open : Closed : Cancelled sheets.
0
propolisAuthor Commented:
Hi,

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

Is this what you want to see

Eddie
0
Martin LissOlder than dirtCommented:
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.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

propolisAuthor Commented:
Thank you
0
Martin LissOlder than dirtCommented:
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

0
byundtMechanical EngineerCommented:
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

0
propolisAuthor Commented:
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
0
Rory ArchibaldCommented:
The formula is looking at column E (Log Date) not H (Close date)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
propolisAuthor Commented:
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
0
propolisAuthor Commented:
OK,

That seems to work now.

Thanks for pointing that out

Eddie
0
propolisAuthor Commented:
Thanks for pointing out this pointing error
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.