05-13-2020 03:23 PM
05-13-2020 03:23 PM
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!
05-20-2020 08:51 AM
05-22-2020 08:31 AM
@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.
05-22-2020 08:53 AM
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.
05-22-2020 04:10 PM
@peteryac60 Thank you for your message. I'm tracking 80-100 projects at any given time so I need some VBA code for this.
05-23-2020 09:13 AM
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:
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?