I have a 125 item checklist in excel which is going to be completed for about 120 projects. It needs to be efficiently summarized and that’s where I struggle. Here’s what I’ve done recently with only 15 projects.
Questions all have a mandatory response (yes, no, DNA) as well as an optional free form comment. An answer if ‘no’ indicates a potential issue.
For the smaller project, I copied and pasted each of the separate checklists onto a single tab, one below the other, on a new spreadsheet. Hidden columns were used to identify the project, office, key personnel, etc.
I then used a pivot table to summarize the results for the group as a whole, and use slicers so we can analyze results by office, key personnel, and industry group.
On each of the individual checklists I used a filter column to filter out every YES or DNA response that didn’t have a comment, to result in a short report with only ‘teachable moments’ for each project, and exported to PDF. The PDFs are then shared with the project manager and their supervisor. It would be great if the PDF conversion and sharing could also be handled within excel.
due to confidentiality issues we cannot share the entire master spreadsheet with the entire group.
We expect to repeat this process twice a year and to expand it to 200 projects in the near term.
SADLY OUR IT Department is quite fearful of any Excel VBA, and macros are automatically disabled.
any ideas would be greatly appreciated.
Questions all have a mandatory response (yes, no, DNA) as well as an optional free form comment. An answer if ‘no’ indicates a potential issue.
For the smaller project, I copied and pasted each of the separate checklists onto a single tab, one below the other, on a new spreadsheet. Hidden columns were used to identify the project, office, key personnel, etc.
I then used a pivot table to summarize the results for the group as a whole, and use slicers so we can analyze results by office, key personnel, and industry group.
On each of the individual checklists I used a filter column to filter out every YES or DNA response that didn’t have a comment, to result in a short report with only ‘teachable moments’ for each project, and exported to PDF. The PDFs are then shared with the project manager and their supervisor. It would be great if the PDF conversion and sharing could also be handled within excel.
due to confidentiality issues we cannot share the entire master spreadsheet with the entire group.
We expect to repeat this process twice a year and to expand it to 200 projects in the near term.
SADLY OUR IT Department is quite fearful of any Excel VBA, and macros are automatically disabled.
any ideas would be greatly appreciated.
