Hey All, I am currently dealing with data from a manufacturing center, where they have a list of parts being used to manufacture products. The center has a list of 500 parts and about 20 different products being manufactured. The sheet that I have contains information about the parts, its costs etc etc (A total of about 45 columns). My task here it to

1.Segregate the parts information into individual products, while ensuring that the information is updatable. Namely, if one of the fields in the parts table change, it should change in all the relevant products tables too. If one adds a new part in the first table, the other tables should also reflect the change.

2. I also have to create a summary sheet for the individual products, using various IF / Else conditions. In addition to the summary sheet, I have to run scenarios like what would happen if we want to run so much of this product at such a time, for which I have to write macros / formulas. These would also have to update themselves if the primary parts database is updated.

3. Lastly, I also have to set up a pivot table, which would calculate values, for example, management would like to pull up information about a certain vendor only and a certain shipping time and look at the total cost of the parts from them. This means that a dynamic formula is needed in the pivot table.


I am looking at a way to implement this solution, namely, is Excel the best option or would you say creating a database in access would suit it better. How can I ensure that all the product sheets are connected to the parts sheets and update themselves automatically?

It would be great if you could provide me with any direction here.