Automatisation in Tables and Roadmap in Excel

[silverhammer]

Hi Community

I have a tedious task for which I regularely need to create a ProjectRoadmap, Trimester plannigs and charts of our Workload on a regular basis.
The data for all those charts comes form a Jira Export. Sadly the charts in Jira do not meet our requirements and I have to create them in Excel. So I need your help!

I have attached to this post my draft including test-entries for epics, features, tasks and milestones.
The draft contains multiple tabs:

1. Export Jira - this shall be the table where I regularely copy-paste the new Jira exports
2. Roadmap - a Chart which shows all Epics and Milestones from the project on a Roadmap
3. Planning Trimester - a chart which shows the epics and features which ar running in this trimester
4. Wodkload - a chart which shows what features cause how much workload during the trimester
5. Config. - where I manually enter the start- and enddate of the trimester

While for now all Tabs are filled manually, the 3 blue tabs (Roadmap, Planning Trimester, Workload) shall be completely created automatically based on the data from the Jira Export and the Settings in the Config-Tab.

I would like to start with the "Workload" Tab, as the table used in there might be the most complex and learnings from here can be applied to the other Tabs as well.

To create the table in the "Workload" Tab automatically, the following steps need to happen:

1. The fields "Zusammenfassung", "Gantt Start Date", "Gantt Finish Date" and "T-Shirt Size" from the Tab "Export Jira" need to be copied into this table. Only Features which from its duration are taking place during the planning phase (see tab "config") are copied!
2. The date-fields need to be formatted correctly
3. De column "Dauer" (=duration) needs to be added with the according formula
4. The column "Aufwand Tot. in PT" needs to be created and filled with the according formula
5. The columns with the months need to be added based on the selected start- and end-date in the Tab "Config".
6. The workload for a feature is spread equeally across its duration. The total workload for the duration of a feature in a certain month shall be displayed in the according fields.

When the Table is working properly, we should be able to create the chart above it automatically as well. This then is step 2 of the process ;-)

Do you have any idea how to create the needed table automatically?
Thanks in advance for your help!

Silvehammer

 

[JoePublic]

Cross-posted: https://www.excelforum.com/excel-general/1433751-automatisation-in-tables-and-roadmap-in-excel.html

Please take a minute to read the forum rules (here and elsewhere) and follow them in future. Thanks.

 

[p45cal]

I'm having a look at this and have got to this:



which is different from your results (I've added a decimal for clarity).
Could you confirm that the above is at least close to what you expect from the Export Jira data you have in the file you attached?