Blank.JPGHowItShouldBe.JPG
Hi All,

I need your help again guys with my another project that required multiple dropdown list with IF functions too.

So, I have some Parts Code database that each of the code represents different Parts Name and also Vehicle Name. I successfully make the field automatically filled using VLOOKUP code.

However, each of this Part contains more of Sub-Parts that differs between each other. Some Parts consist of 2 Sub-Parts, some others contain 4 and 8 sub-parts. So, the user of this Excel will be required to fill the Date column, Parts Code column and then further they also to fill the "Parts Used" column manually on daily basis. I have tried to minimize the risk of input error with INDIRECT function by creating drop-down list of Sub-parts depending on the parts code.

So, basically what I'm trying to ask for your helps is how to make the "Leftover Parts" column to be filled automatically.

For example, KO0398-S993-00 (aero_mudguard) parts have 8 sub-parts and in January 31, 2013, the user used 3 parts (K, L, O). So, there should be 5 sub-parts left (S, Y, Z, N, U). The question is how to generate/ fill the "Leftover Parts" column with S, Y, Z, N, U automatically.

Another example, 7603S-TA02 (mud_guard) consist of 4 sub-parts and February2, 2013, there were 2 parts used already (E,I). So, at the end there should be 2 sub-parts left (H, J). The question is how to generate/ fill the "Leftover Parts" column with H, J automatically.

I attach the Excel file and also the screenshots of it.

Many thanks in advance guys. Really appreciate your helps and efforts.