Creating Dropdown Menus and Populating other cells

Hello I am limited in my excel knowledge so bear with me. I wasn't sure how to search or what to search for with this.

So I work on Wiring Harnesses and I need to go through them and sometimes replace connectors on them.

At the moment what I have done is made a table with a list of the all the connectors.

Then I have a cell next to the connector name and part number which I have drop down function to say whether the connector was replaced or not.

The issue is some Wiring Harnesses have 2, 3 or even 8 of the same connector. To make sure that I go through the harness thoroughly I list every connector down and so I can end up with a list that has 8 connectors with the same part number but a different name.

When I am done that means that I have to go through the list and because the connectors are not in part number order it makes it difficult to confirm exactly how many of each connector was replaced.

Now what I thought might be a better option is to have a second table or sheet and say if I choose from the drop down menu that I have replaced the connector that it will populate another cell with 1 and then if I choose another connector with the same part number it will then change the cell from 1 to 2 and so on, so if I replaced all 6 or 8 of a connector with the same part number in the next table or sheet I can simply click there and it will say that I replaced 6 of this connector and 3 of that connector. This will give me a simple table to look at to help with billing.

I realise that this will probably require making a new table with all the connector part numbers in but in that case only a single incidence of each connector part number.

I'm really hoping this makes any sense and anyone has seen a YouTube tutorial or has done something similar themselves.

T.I.A