Trying to figure out what is best to showcase the workbooks overview

Hi all,

I've inherited an Excel workbook tracking a number of items and quantities. There is a general overview sheet, and then numerous other sheets breaking down what items are needed for different projects. It has some duplication across the sheets for this reason!
However, I wanted to give it an overview sheet, where it can be easily seen what is allocated where and if the quantity needed is available per project.

I've done this before in a previous job role, but as they say "use it or lose it!" and I have lost it. I cannot remember how I set it up - it was pre-covid. I think Pivot tables/charts were involved, but it's not offering me all I want when I try it now. Maybe I'm having a mental block, or maybe there is a much better way of doing this now! I'm pretty savvy with Excel, so fire away - think I just need the mental push.

Any help appreciated!