Building a report is the work of a day, or maybe a week.

Laying out a table is the work of a day, max. It might be spread across a while because you work on it and come back, but in the end, it's like 8 hours of work.

But filling in your table? That's the part that happens over thousands of hours spread across twenty years.

So in my view, laying out the data in a way that's robust and easy to use is the critical part. If your data is set up well, then slapping a new report on top of it is easy. Adding new data is pretty straightforward and intuitive. But if it is not set up well... well.

So I think you'd be well served to follow the classic rules of building tables. This is more like an SQL or Access question, but it applies a lot to Excel as well.

I think generally you're on the right track. Rather than trying to anticipate what everyone will need to do, just make your table good and useful.

Broadly speaking, a table has:
1) Headers across the first row, naming what's in each column
2) One Object / entity per row
3) One piece of data in each cell, that is a property of that Object

The critical thing is deciding what each "Object" is. Is it the part #?
Usually each Object should have a unique Key that is data value that is one-and-only-one, where there is one Key per Object, and it never has another Key, and no other Object has the same Key. If you have a data value per Object that is already like that, it's usually a good bet to use that.

Every column should have the same type of data. Whether it's a date/time, an integer, a text description, a Boolean, whatever; everything in the column should be the same type of data for each Object.

Generally speaking, the table should record only data records; try to avoid sticking "helper columns" in that transform and process data. If you want something really robust, your data should flow like:
Records Table => Processing Table => Report
(That's where having unique one-and-only-one Keys on your data records helps; it makes lookups really easy to implement, because they're a chicken-and-an-egg relationship; lookup functions were written assuming your data has unique Keys, so pulling data into your Processing Table becomes a trivially easy activity).

Once set up, the "design" of the Records Table should be frozen; adding new record rows is something anybody can do, removing old records is something to double-check, and adding/removing columns requires a boring 30-minute review meeting. That kind of logic.

Then people can pull the Records Table (using an External Data Link, say?) to set up their own Processing Tables to create whatever the hell kind of report they want.