Summary Sheet Task tracker

[MarkRush]

Hello All and thanks in advance for any help. My organization decided to pull the plug on an project tracker database and put my team in a bind and we need to go back to excel real quick . Each team member has a master workbook that could contain anywhere from 5-25 tabs ( for for each open project). I need a summary sheet that will list all uncompleted tasks for the past 7 and next 7 days.
Project name is in merged cell A3
Due Date is in Column 2
Completed indicator is in Column C and is a drop down list with Y/N

On the summary sheet I would like it to show Customer Name, Due Date, How many days till completion or how many days overdue and all of the data from columns D thru I , Ideally overdue tasks would use a red font

I would like the summary data to refresh every time the workbook is opened or end user moves from project tab to Summary tab

We are using the latest version of O365, but some users are using 64bit some are using 32 bit , everyone is on Windows 10

 

[dmt32]

The problem with using Excel in such a manner is that it was not designed to work in a multi-user environment. Excel has a "share" functionality but it is best avoided. When information sharing is needed, you would, in my view, be better exploring a database application like Access.

If this is not possible and Excel is your only choice you will need to think carefully how the data is stored & shared. You say each team member has a master workbook & that you want a Summary workbook to gather specific data from them? I would suggest that you do it the other around.

Have one master workbook (database) that contains all the data in a single table (flat file database) which user can access across your network. You would set their workbooks to read/write to your master workbook. This will likely involve, depending on complexity of your requirement, extensive VBA to manage but at least data management will be consistent.



This is just my personal view, others here may be able to offer alternate suggestions.



Hope Helpful



Dave

 

[MarkRush]

The problem with using Excel in such a manner is that it was not designed to work in a multi-user environment. Excel has a "share" functionality but it is best avoided. When information sharing is needed, you would, in my view, be better exploring a database application like Access.

If this is not possible and Excel is your only choice you will need to think carefully how the data is stored & shared. You say each team member has a master workbook & that you want a Summary workbook to gather specific data from them? I would suggest that you do it the other around.

Have one master workbook (database) that contains all the data in a single table (flat file database) which user can access across your network. You would set their workbooks to read/write to your master workbook. This will likely involve, depending on complexity of your requirement, extensive VBA to manage but at least data management will be consistent.



This is just my personal view, others here may be able to offer alternate suggestions.



Hope Helpful



Dave

Its not a shared workbook in that manner, every user would have their own workbook, The summary sheet would just be to give them a snapshot of what tasks are coming up. We know its not an ideal situation, but our IT planning department has an 18 month backlog on projects.

 

[dmt32]

Its not a shared workbook in that manner, every user would have their own workbook, The summary sheet would just be to give them a snapshot of what tasks are coming up. We know its not an ideal situation, but our IT planning department has an 18 month backlog on projects.

Its just my personal view but the issue you are likely to have is one of data maintenance - keeping all the data in one central place on your network & creating ability for your users workbooks to read / write the data to it should, although not perfect, provide a more robust data management solution as against trying to gather it in from each team members workbook.

Dave

 

[MarkRush]

Its just my personal view but the issue you are likely to have is one of data maintenance - keeping all the data in one central place on your network & creating ability for your users workbooks to read / write the data to it should, although not perfect, provide a more robust data management solution as against trying to gather it in from each team members workbook.

Dave

Dave, thanks for the reply but again its not a shared or data maintenance.. Maybe i did a poor job explaining.. Each team member has their own workbook that could contain up to 25 worksheets, All of these sheets have a list of tasks that need to be tracked. the tasks most likely are being completed by people that are not members of the team and my teams job is to monitor those tasks and assist if needed. The summary sheet I am looking for is just a convenience for them so that they can open the spreadsheet and see what tasks are coming up, what is overdue etc., without searching thru multiple tabs. ideally something like this. Copy rows based on cell value "Open" from multiple sheets

This is a short term solution, we know we need a better tool, however funding was cut for purchasing an off the shelf solution and our IT organizational programmers have a huge backlog

 

[dmt32]

If you just want to summarise within each workbook then try adapting the posted code - if need further help, useful to forum if you could either provide copy of worksheet using MrExcel Addin XL2BB - Excel Range to BBCode
or better still, place copy of the workbook with dummy data in a file sharing site like dropbox.

Dave

 

[MarkRush]

OK Here is the code I have right now. I have deactivated the worksheet call section so it doesnt run into issues

Issues I am having are as follows:

1 Need to get customer name into Column A
2. Because the date column in task sub sheets are formulas, When Copied into summary sheet it creates a circular reference in cell B3
3. Code is deleting my header row 1
4. Code doesn't look at just +/- 7 days

Here is the Dropbox Link

dropbox

Private Sub Worksheet_Activate()

'Call Summary

End Sub





Sub Summary()
Dim sh As Worksheet, lr2 As Long, lr As Long, r As Long
Sheets("Summary").Rows("9:" & Cells(Rows.Count, "A").End(xlUp).Row).Delete
lr2 = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For Each sh In Worksheets
        If sh.Name <> "Summary" Then
             sh.Activate
             lr = Cells(Rows.Count, "C").End(xlUp).Row
                 For r = 3 To lr
                    If Cells(r, "C").Value = "N" Then
                        Rows(r).Copy Sheets("Summary").Range("A" & lr2)
                    lr2 = lr2 + 1
                    End If
                Next r
        End If
    Next sh
End Sub

 

[dmt32]

Pasting values only should overcome the copied formula issue.
Unless another here steps in, will take a look & see if can assist but plans with my family this weekend.

Dave

 

[dmt32]

Issues I am having are as follows:

1 Need to get customer name into Column A
2. Because the date column in task sub sheets are formulas, When Copied into summary sheet it creates a circular reference in cell B3
3. Code is deleting my header row 1
4. Code doesn't look at just +/- 7 days

See if this update to your code does what you want

DELETE ALL EXISTING CODES

Place following code in a STANDARD module

Sub Summary()
    Dim sh              As Worksheet, wsSummary As Worksheet
    Dim CustomerName    As String
    Dim lr2             As Long, lr As Long, r As Long
    
    On Error GoTo myerror
    Set wsSummary = ThisWorkbook.Worksheets("Summary")
    wsSummary.UsedRange.Offset(1).Clear
    
    lr2 = 2
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> wsSummary.Name Then
            CustomerName = sh.Cells(1, 2).Value
            lr = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row
            For r = 3 To lr
                If UCase(sh.Cells(r, "E").Value) = "N" Then
                    sh.Cells(r, "A").Resize(, 9).Copy
                    With wsSummary.Cells(lr2, 1)
                        .PasteSpecial xlPasteValues
                        .PasteSpecial xlPasteColumnWidths
                        .PasteSpecial xlPasteFormats
                        .Value = CustomerName
                    End With
                    lr2 = lr2 + 1
                End If
                Application.CutCopyMode = False
            Next r
        End If
    Next sh
    
    wsSummary.Columns(1).AutoFit
    
myerror:
    wsSummary.Activate
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Note:
1 - Customer Name on each sheet must be in cell B1
2 - I have included some error handling just in case your users make changes to their workbook that could upset the working of the code.

You can call the code from the summary sheets activate event or thisworkbook workbook open event (or both) or place a button somewhere, really your choice.

Private Sub Worksheet_Activate()
 Call Summary
End Sub

Private Sub Workbook_Open()
 Call Summary
End Sub

Hope Helpful

Dave

 

[MarkRush]

Dave, Thank you so much for you help, I had to tweak a few things , buts almost perfect . For some reason the 3rd customer sheet isnt updating properly on the summary tab, its just grabbing some random days. Also is there any way to calculate days till due based on current date? And to just get tasks from the past 7 and future 7 days?

here is the updated dropbox
Dropbox File