Pete Mccann
Member
I have a particular question below that I would like to solve using formulae rather than VBA or Pivot tables (I appreciate I have posted a similar request "Can I create these charts by using a PivotChart technique" from 1st August). The reason for this new request is I believe some users of spreadsheets can understand the concept of formulae easier than VBA or Pivot Tables (if they are not familiar with them) especially when we distribute spreadsheets to other users.
However, it often amazes me how responders on this forum arrive at their solutions to particular problems. This is obviously a reflection of the responder’s level of experience in Excel and their understanding of the various functions involved.
So I would not necessarily just like the answer to the problem (although that would be very nice) but I would like to understand the thought process involved in solving the problem. As the old saying goes, “Give a man a fish, and you feed him for a day. Teach a man to fish, and you feed him for a lifetime.”
In the attached file, I have the data involving the delivery of documents to a client linked to various tasks in a MS Project Plan of Work (POW). I have copied these dates from the POW into the spreadsheet. This key information is in columns B:C. Each document has a “Forecast date” (for delivery), an “Owner” (a member of the project team) and a Status (“Complete”, “Overdue” or “Not Yet Due”). The Status values are determined from the POW.
I would like to develop a set of formulae in the cells I5:AY22 to show the following (in the attached example the coloured boxes have been manually drawn as an example):
However, it often amazes me how responders on this forum arrive at their solutions to particular problems. This is obviously a reflection of the responder’s level of experience in Excel and their understanding of the various functions involved.
So I would not necessarily just like the answer to the problem (although that would be very nice) but I would like to understand the thought process involved in solving the problem. As the old saying goes, “Give a man a fish, and you feed him for a day. Teach a man to fish, and you feed him for a lifetime.”
In the attached file, I have the data involving the delivery of documents to a client linked to various tasks in a MS Project Plan of Work (POW). I have copied these dates from the POW into the spreadsheet. This key information is in columns B:C. Each document has a “Forecast date” (for delivery), an “Owner” (a member of the project team) and a Status (“Complete”, “Overdue” or “Not Yet Due”). The Status values are determined from the POW.
I would like to develop a set of formulae in the cells I5:AY22 to show the following (in the attached example the coloured boxes have been manually drawn as an example):
- For each Owner, a sum of the number of documents for each time period between Start Month / End Month.
- For each of these values, we should see (with a coloured indication) if these documents are either “Complete”, “Overdue” or “Not Yet Due” vs. an Owner.
- If this summary data set can be created, could it also be made into a chart so we could show “today()” as a line on the chart to show progress or delay but have a similar appearance to the area in G3:AY22?