Forum Discussion
Getting list of project names from another sheet and keeping static data in line with project names
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.
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
peteryac60 Thank you for your message. I'm tracking 80-100 projects at any given time so I need some VBA code for this.
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