Earned Value Workbook - Auto Create Date Range based on user inputs

[Luca591]

Hi Everyone,. I am working on creating an Earned Value workbook to track the progress of several projects. Each worksheet in my book represents a Project which usually has some subtasks with their own budget and schedule. I have a summary/input table setup at the top left of my worksheet that captures info such as the Project Title, Code, Budget, Start and Finish Date etc... Below the summary table I have a subtask table that adds subtasks with their own start and finish date and budget.

Off to the right I am using this info to setup a time phased budget in a separate table. This time phased table lists the subtasks in each row and fills in the budget by week based on the total budget divided by the #weeks based on the start and finish date. Following?

Is there any way to post a workbook to share?

Here's where I need help. I think this is going to be a VBA task, maybe it requires a GUI/input dialog, but as the user is populating the subtasks with their own respective schedule and budget, i need that info to update my time phased table off to the right with the same info and fill in the budget per week based on the dates i setup in the Subtask table. I put a an example of what I am describing below. I would like the time phased budget table to auto setup the range based on the min and max dates provided in the sub tasks.

One other thing I need help with is that i want each worksheet to be titled based on the last 6 letter/numbers of the project code. So whenever I create a new project worksheet, the user should input the project code and the tab/worksheet would be titled whatever the last 6 of the project code.

Thanks in advance.

Subtask table example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Budget[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]200[/TD]
[TD]08/31/17[/TD]
[TD]11/30/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500[/TD]
[TD]01/01/18[/TD]
[TD]01/15/18[/TD]
[/TR]
</tbody>[/TABLE]


Time phased budget/schedule table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]08/01/17[/TD]
[TD]08/08/17[/TD]
[TD]08/15/2017[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]01/01/18[/TD]
[TD]01/15/18[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dmt32]

Hi,
one way to share your workbook here would be to place it in a dropbox & provide link to it.

Dave