pull data from separate tabs to "dashboard" tab, removing duplicates and counting % of cells poplualted, etc

[kenton71]

I have a spreadsheet with six tabs (one for each team member) and one additional tab to be used as a dashboard. I'm trying to pull data from each tab and have it populate the dashboard tab.

Each team member has the same layout on their tab and uses the same status names. So, Column E has a unique project number (which may or may not be repeated several times on their tab, depending upon the size of the project), columns C and I are always the same for Column E (showing due date and project name). Columns F, G, and H will have unique data on every line as they relate to the individual items on each project. F, G, and H will either have information or not. And we just need to capture how many times for the project a cell in those columns is populated.

What I want to end up with on a new tab:
Status from column A ("not started", "in progress", "complete", "on hold", or "cancelled" -- each team member uses the exact same words), Project Due Date (column C), Project # (column E), Project Name (column I), Team Member Name (from column B), % of cells in column F for that project with an entry, % of cells in column G for that project with an entry, % of cells in column H for that project with an entry. I think the trickiest thing is that some projects only have one line on the spreadsheets and some have more than 70.


Example of how the end product should look:
In Progress 2/1/2019 ABC123 Project Lion Carla 50% 75% 10%
On Hold 3/5/2020 BDG425 Project Tiger Bill 46% 58% 100%

 

[frank_AL]

sorry, one more thing. would like to add a column showing the number of days until (since) the in market date. I was trying to just put in the formula in a new column on the dashboard =today()-B1 but it is coming up with an answer that is too large to display.

I have added this functionality. I check to see if the project is not set to "complete" in column A. If "complete" I'm not added the calculation.

 

[frank_AL]

hmm... and I just noticed that it's not grabbing all the records. for example, I have some projects that don't have a project number yet (not all will). It isn't pulling those over.

I've updated code to pull over any record where Project ID is blank.

This code has corrections for the 3 items I've quoted in this and the prior 2 posts.

Option Explicit


Sub SumData()
Dim ws As Worksheet
Dim ctws As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
Dim data(1 To 10) As Variant
Dim projcnt As Long
Dim phasecnt As Long
Dim sapcnt As Long
Dim shipcnt As Long


Set ctws = Worksheets("Dashboard")
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Dashboard" Then
        lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
        ws.Range("A2:I" & lastrow).Sort key1:=ws.Range("E1"), Order1:=xlDescending
        projcnt = 1
        phasecnt = 0
        sapcnt = 0
        shipcnt = 0
        For i = 2 To lastrow
'            If ws.Cells(i, "F") = "yes" Then phasecnt = phasecnt + 1
'            If IsNumeric(ws.Cells(i, "G")) Then sapcnt = sapcnt + 1
'            If IsDate(ws.Cells(i, "H")) Then shipcnt = shipcnt + 1
            If Not IsEmpty(ws.Cells(i, "F")) Then phasecnt = phasecnt + 1
            If Not IsEmpty(ws.Cells(i, "G")) Then sapcnt = sapcnt + 1
            If Not IsEmpty(ws.Cells(i, "H")) Then shipcnt = shipcnt + 1
            If ws.Cells(i + 1, "E") <> ws.Cells(i, "E") Or IsEmpty(ws.Cells(i, "E")) Then
                data(1) = ws.Cells(i, "A")
                data(2) = ws.Cells(i, "C")
                data(3) = ws.Cells(i, "E")
                data(4) = ws.Cells(i, "I")
                data(5) = ws.Cells(i, "B")
                data(6) = phasecnt / projcnt
                data(7) = sapcnt / projcnt
                data(8) = shipcnt / projcnt
                data(9) = projcnt
                If data(1) <> "complete" And IsDate(ws.Cells(i, "C")) Then
                    data(10) = Date - ws.Cells(i, "C")
                Else
                    data(10) = ""
                End If
                projcnt = 1
                phasecnt = 0
                sapcnt = 0
                shipcnt = 0
                nextrow = ctws.Cells(Rows.Count, "A").End(xlUp).Row + 1
                ctws.Range("A" & nextrow & ":J" & nextrow) = data
                projcnt = 0
            End If
            projcnt = projcnt + 1
        Next i
    End If
Next
ctws.Columns("F:H").NumberFormat = "0.0%"
ctws.Columns("I:J").NumberFormat = "0"
ctws.Columns("A:J").EntireColumn.AutoFit
End Sub

 

[frank_AL]

kenton,

On the issue of the difference between today's date and the date value in Column B on the Dashboard. I had several format inconsistencies with the original data you provided. I was able to find and correct these so that the code could be tested but that may need to be something you check in your file.

I still cannot determine why there are duplicates or some projects are missing on the Dashboard.
My guess is that it might have something to do with how I am determining the lastrow on each Worksheet. If that function is not happening properly it would cause existing records to be excluded. The code assumes a valid entry in Column A. That was true of the test data provided. However if that is not true with the actual data it could be the reason not all projects are being reported.

Can you tell me which column has some entry in each and every record? If not, I will have to find another way to determine how many records to process on each employee worksheet.