Help with complex inventory in Excel

Hey all, new to the forum, conducted a search but found nothing matching my situation.

My situation is as follows:

The company I work for makes manipulator arms for handling tools and materials. Some are custom, but most are standard. We have an existing inventory of all loose piece parts, but we'd like a way to track arms that are built and in stock. On average, these arms consist of 50+ individual fabricated pieces, so manually editing multiple lists will be time consuming. We'd like to have tabs of the individual models and their parts, and have the parts quantities linked to the main page master inventory, preferably with the ability to just +/-1 assembly and have it adjust the parts accordingly.

I.E.- Say you have parts A-Z. Assembly "1" uses parts A-F, M-O, and Y. Assembly "2" uses parts A-F, P-T, and X. If we sell 8 units of "1", and 3 units of "2", we would like to be able to subtract that amount of units from each assemblies tab, and have it subtract the appropriate number of parts from the main inventory, instead of us having to manually subtract 11 of A-F, 8 of M-O & Y, 3 of P-T & X.

If anyone has a solution for this, we'd greatly appreciate it! FYI, we're a small operation, 8 people full time, and we've looked into inventory programs but it's just not something we can afford yet, hence why we're trying this route.

Thanks all! Have a good one.

If you have the full Office Suite, you will have Access database creation ability. I'd suggest setting up tables in Access. Take a look at the Northwind database which comes with Access to get an idea of what a relational database can do for you.

Well... there's the template for inventory with excel...

But, the incremental cost of Access for you is like, $100 each. I'd have to look deeper to get an idea of what a package license for a small business would be... anyway, my point is, weigh the benefit of Access vs the cost before dismissing the software purchase out-of-hand.

EDIT:
do you guys use barcodes?

So mae a start and post back for specific help

there was a typo in my previous post.

Start your project and we'll help, but I for one haven't the time to build a project from scratch for you