@JPL_76
...else I'll just accept I've got to look at less automated options.
That wasn't my point, certainly. Your approach from the start was "automated" primarily through the use of VBA, automating what were otherwise really manual steps. I sometimes call that type of use "the brute force" approach to automating. I've seen it happen when IT folks know procedural languages (like VBA) that they'll "automate" a process via VBA or a macro, when it could be approached via built-in Excel functions and operate faster and more reliably. It's more a matter of the mindset with which one approaches the task.
I learned early on in my computing days (which were more than 50 years ago, so we're talking pre-PCs) to think of data more in terms of tables of data--sometimes two-dimensional tables, sometimes multi-dimensional. And then one applied the computing power of whatever system existed to extracting/summarizing/cross-tabulating the data from those tables by means of formulas.
Later in my career, even though I wasn't technically in the IT division of my company, I became the director of a project to develop an HR/Payroll database system. I was offered (and accepted) the job, because I could "speak" database "languages," because I understood the concepts. At the risk of over-simplifying things, let me describe one of my primary learnings from all this, whether we're talking of tracking status of projects or people: As humans, coming at such a task, we can easily be tempted to track the employment history of a person by creating a history table for each single person, noting dates for each transaction--hire, promotion, transfer, demotion, promotion, leave, return, retirement, etc--and doing the same for each person in the organization. But it actually is far more efficient to have a single table that tracks all that for every employee, and learn to write queries or reports that extract the most recent status for each, by location, by division, by classification, etc. Looking at such a comprehensive table with our eyes, it's bewildering; but by using the computer to do the first (and maybe second and third) look, by sifting and sorting according to defined criteria, you can extract meaningful summary data from that single transactional database.
The mistake (if I may be so bold) that you've made is to approach this so that it looks neat (and you've even made it pretty) at the raw data level (at the Input end of things). But what you could have done is make it be comprehensive at the raw data level, to make sure you are capturing all relevant data on each status update for each project etc, and then use Excel's fantastic functions (e.g., FILTER, XLOOKUP, Pivot Tables, etc) to extract data for Output purposes, creating those nice looking status reports at the end of the line. Excel is truly remarkable in what it can do when it comes to data manipulation; I would go so far as to say only rarely is VBA needed.
I don't know if this would help, but certainly there have been books written on using Excel in connection with project management. Here's one example--I have no direct experience with it, just showing it as an example. There are probably templates as well. Perhaps others can make suggestions along those lines. My goal here was simply to try to point you in one direction that you might want to consider, hopefully a direction that could end up being more highly automated, not less, taking more full advantage of Excel.
