I use Excel to control parameters for design of automation equipment. I would like to create a dynamic bill of materials in Excel based on which items/configurations in an assembly are present.

To put it simply, if an assembly calls for a 4 inch long bolt and I change the parameters of the assembly so that it then requires a 3 inch bolt instead of the 4 inch bolt, I want the line item that encompasses the 4 inch bolt to disappear and the line item for the 3 inch bolt to appear. Under certain conditions, line items will need to be removed and will not be replaced by other parts. In this case I don't want my table to have empty rows. Is there a way to accomplish this?

I'm not against writing VBA to help if necessary, but I'm hoping to be able to do this without.