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%
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%
