Getting list of project names from another sheet and keeping static data in line with project names

Highlighted
Occasional Contributor

I want to get Project Names from a dynamic Master List in Excel and put those Project Names on a second worksheet where I can manually enter additional information related to the project, for project management purposes. If a Project is removed from my Master List then it should be removed from my second worksheet. In addition, I need the manually entered on the second sheet to stay aligned with the right project. I've attached a sample copy of my workbook.

 

Thank you in advance for your assistance!

6 Replies
Highlighted

@R_Ska Hello! I'm not sure what your inquiry has to do with Microsoft Teams. I'm moving your message to the Microsoft Excel space. Please be careful to post future questions in the relevant community - thank you! 

Highlighted
Highlighted

@R_Ska I managed to figure out how to get the list of project names in the second tab using a hidden column numbered sequentially and using 

=IF(ISBLANK(INDIRECT("Projects!B"&A3)),"",INDIRECT("Projects!B"&A3)) but I still cannot figure out how to keep the project names on the second tab in line with the correct static data on that tab if the Project Names are deleted on the first tab. 

 

Can anyone assist? Thanks.

Highlighted

@R_Ska

 

The solution you have outlined works ok.

 

A question - if you filter on the first sheet (say on the Stage column) the Year at Glance sheet will stay the same i.e. will not filter. Are you ok with this? 

 

I do not think you can delete a row from the first sheet and have the corresponding row in the second sheet deleted using conventional Excel commands.

 

You can do this by writing a VBA macro to compare the sheets (and incidentally to allow the filter to carry through) but it requires a bit of effort to get it done.

 

How many projects do you think you will track this way? if it is less than 20 I would recommend you just manually delete them - it will be quicker. Of course, you could just filter out the Completed projects and then they will be hidden from view. This might be a better approach as you will retain the history.

 

Maybe not the answer you wanted but I hope it will help.

 

thanks,

 

Peter

 

Highlighted

@peteryac60 Thank you for your message. I'm tracking 80-100 projects at any given time so I need some VBA code for this. 

Highlighted

@R_Ska 

Hi 

 

I have attached a spreadsheet with some VERY SIMPLE VBA code which will delete a row on the second sheet if you delete the corresponding row in the first sheet  HOWEVER

 

Because this is simple code and does not have the normal error trapping it can fail if:

 

  • You change the headings on one or both sheets
  • You delete a row in sheet 1 , then re-insert ; it will be deleted from sheet 2 and will not be re-inserted
  • If you filter the data might lead to unexpected results

In short, even though this seems like an attractive solution I will still strongly recommend that you manually delete your entries from both sheets. The effort in completing the code properly cannot be justified.  Even with 100 projects I do not imagine you are deleting many every day so it would be simpler to just do this manually.

 

BTW - did you consider just filtering out the projects you no longer needed or did not want to see?

 

Good luck!

 

Peter