Need help in finding a formula which finds the above level for automotive components!

Good morning,

I think I will give you some brief background on my Excel problem before explaining it further as it is quite complex so please do bare with me. I am usually pretty good with excel but this is a bit too scary for my liking!

Basically, we have a bill of material which we can import from our internal system into excel (this is all the components of a fuel tank which go into making it such as brackets, valves etc etc). The way our system works is that the finished fuel tank is at level 1. Anything that goes into level 1 is a level 2 - anything that goes into a making a level 2 is a level 3 (and so on). For clarity sake:

LVL 1 - Fuel Tank Assembly Finished
LVL 2 - Fuel Tank Assembly Painted
LVL 3 - Fuel Tank Assembly Unpainted
LVL 4 - WELDED Fuel Tank

As you can see, LVL 2 will go into a LVL 1 and a 3 will go into 2 etc. This goes all the way down to the very start of the construction of the fuel tank with all sorts of complicated parts which aren't particulary important which make up a fuel tank.

Hopefully, so far so good.

For each of these component parts we have a STOCK ON HAND amount (i.e. how much stock we have on site of that component). So for example, we could have 20 finished assembles but 26 painted, 18 unpainted and 12 which have been welded.

To make things easier, lets say a customer requires 100 new finished assembles. We already have 20 on site and so only require 80 to build. Though we have 26 painted fuel tanks, this means we only need 54 to build (80-26=54) - and so on down the list.

Though this seems simple to drag down a simple calculation, my main problem occurs as the levels do not nicely follow a 1,2,3,4,5,6,7 sequence, rather we will have a run of the same levels in the table. Remember, any given level must always look at the level above it as it goes into it (6-5, 5-4, 4 looks at 3 etc).

An example of this section in my spreadsheet to give an idea:
================================================================
pic.JPG
===============================================================

So hopefully (if I have explained well), I need to find a formula that looks at the next level above (not the cell) and use that to minus the level above from own stock level to calculate how many are required to build. The key is to remember we are looking for the above level.

The idea is then to be able to be able to export many bill of materials for different parts with the formulas already in place though this may be a bit too complex!

I hope this makes sense, please ask for any more info if you need it. It has my mind boggled and thought I would try and find a forum to help. I will attach my spreadsheet to hopefully make it much clearer. Thank you for any help you can give me, it is greatly appreciated!