My company uses a spreadsheet for an assembly bill of materials. Most of the data in the spreadsheet is populated automatically, but the engineers have to type in a value next to each item to determine if it is to be purchased or manufactured (commonly known as a "make/buy" list).

I am attempting to speed-up the process by making the spreadsheet automatically assign some of those values based on the assembly structure and user input. Basically, when the user enters "PURCHASED" next to an item that is a sub-assembly, I would like to automatically enter "PURCHASED SUB-COMPONENT" next to all of the items within that sub-assembly. I have made a very simple example spreadsheet to illustrate (see attached). Thanks!

Auto classify example.xlsx