How to create a project tracker with 2 worksheets that tie together

[josephdepasq]

I am trying to create a project tracker in Excel. I would like this to have 2 parts and I don't know the best way to tie them together. The first part is an excel sheet that would contain the project information in fields such as client, ID#, location, project dates etc. These fields would be static.

Status	Project description	FPID #	Project #	Client	Engineer	UC	Let date	Cert date	Spec date	Prod date

The second part tracks communication back and forth between the client. This would allow us to fill in fields when the project is first created (plans date) and then track when we send out items (sent date) and when we receive responses (received date) . We communicate with these clients generally 3-6 times over the course of the project which can last over a year.

	Plans dated	Sent date	Due date	Received	Item received	log updated	Comments
Scope		10/31/22	11/31/22
Initial		10/31/22	11/31/22	12/01/22
30%		10/31/2022	12/21/2024
60%
90%	N/A
100%

Is there a way to make a workbook that contains an excel sheet for the first worksheet which then connects to a second worksheet which contains the tracker worksheet?

Ideally when I add a new project to the first worksheet it would generate the second.



I am open to other ways of managing this.

Any input is appreciated

 

[Candyman8019]

I would likely have the first sheet list all of your projects with the details you have identified. When a new project is added, a new sheet is created with the project number as the tab name. Then you can hyperlink from your main sheet to any project very easily.

For automatically adding a new sheet, a bit of VB would go a long way, as long as macros aren't disabled in your organization. I would have the base sheet created and hidden...then when you need a new sheet added, just make a copy of that one and rename it accordingly.

Try this: https://1drv.ms/x/s!AvvvWgHlNEgNp95uRhKLwypDb6tWdA

 

[Candyman8019]

I guess I should have explained a bit more about that sheet. As you're filling out the project info, when you enter the project number, a cell will populate at the end of that row. When you select that cell, the new tab will be created for you.

 

[josephdepasq]

Thank you, I think this may work very well I appreciate the second comment as I didn't see how the new tab populated, very handy. I guess the only downside is that there will be multiple sheets, we generally run about 30-40 open projects but since the project page works as the index I think it will be fine. Is there anyway to use a name or letters within the project number cell. Rare occasions they are alpha-numeric and I notice it doesn't handle letters on the project index sheet although it does generate it on the tracker sheet. Thanks again!

 

[Candyman8019]

There should be no restriction to using alpha characters. Looks like I was missing a couple of quotes in the formula. Download a fresh copy and try it again. You can also remove the "PR" in front of the project name if need be.

ProjectWorkbook.xlsm

Microsoft Excel Workbook

1drv.ms

 

[josephdepasq]

Just tried and now it's not generating the second tab looks like the formula didn't save?

 

[Candyman8019]

Hi Joseph. Please close excel and reopen the file from the link I sent today. I confirmed that it is working on my end.

 

[Candyman8019]

It's important to note that the max character length for a tab name is 30 characters.

 

[josephdepasq]

It's important to note that the max character length for a tab name is 30 characters.

I have spent time formatting this workbook using the project you sent. After formatting I am unable to get the macro to function and despite searching for how to do this I am unable to code it on my own. I understand how the hyperlink works but I do not understand the macro to generate a new worksheet from typing the project name in the project cell. I am trying to get a better understanding but but it is definitely confusing. Is there a way I can link you to the formatted workbook or another way to get the macro working. Thank you again for all your help

 

[Candyman8019]

Yes. You can use onedrive or drop box to send me a link.