•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Creating Dropdown Menus and Populating other cells
Our take
Navigating complex data can feel overwhelming, especially when managing multiple connectors in wiring harnesses. If you're finding it challenging to track replacements and their corresponding part numbers, you're not alone. Imagine transforming your Excel setup to automate this process: by using dropdown menus combined with smart cell population, you could effortlessly tally how many connectors you’ve replaced with just a few clicks. In this guide, we’ll explore how to create dynamic dropdown menus that not only streamline your workflow but also simplify your billing process.
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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How can I make a single selection drop down auto fill a ton of other cells?I've not used excel much so I'm in way over my head. I've watched a bunch of videos and can get one cell to fill from a drop down but it seems to only work if they're 'lined up' with each other? For example in the 1st picture below, the drop down I have I can get 5 and 10-year to autofill MSUP 1st and 2nd Coats respectively, but only if they're next to each other as shown. I may not be explaining this well, sorry in advance if I am, I pretty much just want to select one product / warranty and have it auto fill all the green. I have templates on another sheet posted below that I've been copying and pasting when I price out jobs but it's just not efficient at all. I suspect that I have to organise or format my 'templates' somehow in order to make the V or X lookup function reference it? Either way, I'm pretty lost, so any help is appreciated, thanks guys!!~\~ EDIT: ok I've managed to get the drop down list to populate the correct items for 1 product by 'tagging' but this is reliant on putting this formula in each of these highlighted cells. My problem now is these cells are 'locked' looking for data from the same area as this 1 product, but I need it to search the entire sheet. When I select the entire sheet for the 'return array' it gives a value error presumably because it's trying to pull more than 1 value and put it in a single cell from my 'tags'. I suppose I could make a sheet for each system template we have but that would be cumbersome to work on. Is there a better way to do this? Am I even making sense? haha =XLOOKUP($B$7,Sheet1!A20,Sheet1!B20) https://preview.redd.it/cfm0r533e2ng1.png?width=834&format=png&auto=webp&s=aabf27687150d1700dd5e99f406106e4f884f17b https://preview.redd.it/l2k8ory9e2ng1.png?width=908&format=png&auto=webp&s=7d0377af01f791ca49d9a0ff71866597d6ee03be https://preview.redd.it/dgm52mnbe2ng1.png?width=978&format=png&auto=webp&s=e5c636d22dc284402b5ebe60fb1da9b531ebc248 submitted by /u/MahoganyDesk [link] [comments]
- How to make a drop down list based on static values of cells in column beside listI have tried looking for the answer to this, but I keep on getting results for contextual multiple drop down lists, which is NOT what I want I work in automotive transportation. We just discovered that we have some information that is not linking correctly in the transportation program that we use. We can use an excel file to upload the corrections, but I need to get it set up first Basically have a list of customer invoice numbers that I need to match to our internal order numbers. I can pull information on the moves based on the vehicle vin number. The only problem is that the only common information between the list from the customer and the information I can pull are the vin numbers and the prices. Neither of which are unique in either list, as many of these we have moved multiple times. Even the dates only generally matched as the invoice date can be several days after delivery and there are even some vehicles that have been moved multiple times on the same invoice I can sort things to group the vins, and then match information by eye. But I am trying to make that a bit easier. What I would like to do is have a column that has drop down list, with the values in the list based on the vin number that is in that row So I want excel to look at the vin in that row, then search the other list (on another sheet) for the same vin and populate the list with the order numbers that match Not sure if it is possible or if what I am saying makes sense, but I figured I would ask before I start having to fix about 13k records fully manually. As it is because of the duplicate information I do not think I can automate or use functions to help with anything else other than arranging the order of the information for the upload I am using legacy excel 2019 Edit: I see that I was not clear enough and need to show some examples. Here is the information that I can pull from our program. I can actually pull quite a bit more, but none of it will match with the other sheet. Note that this is for one (fake) VIN. There are a few thousand individual VINS + A B C D E 1 Order ID PO Number Vin Rate Delivered Date 2 113201 460933 WBAVH13538VMRA825 1,800.00 03/02/2026 3 121139 461019 WBAVH13538VMRA825 400.00 03/05/2026 4 149543 461229 WBAVH13538VMRA825 1,300.00 03/18/2026 5 170875 461472 WBAVH13538VMRA825 1,500.00 04/02/2026 6 180304 461548 WBAVH13538VMRA825 400.00 04/02/2026 Here is the information about the invoices: + A B C D E 1 VIN Invoice Number Amount Type Invoice Date 2 WBAVH13538VMRA825 I647898 75 TAX 2026-04-09 3 WBAVH13538VMRA825 I647897 1500 Transport 2026-04-09 4 WBAVH13538VMRA825 I699603 400 Transport 2026-03-12 5 WBAVH13538VMRA825 I699604 20 TAX 2026-03-12 6 WBAVH13538VMRA825 I699603 1800 Transport 2026-03-12 7 WBAVH13538VMRA825 I699604 90 TAX 2026-03-12 8 WBAVH13538VMRA825 I674724 1300 Transport 2026-03-26 9 WBAVH13538VMRA825 I674725 65 TAX 2026-03-26 10 WBAVH13538VMRA825 I647897 400 Transport 2026-04-09 11 WBAVH13538VMRA825 I647898 20 TAX 2026-04-09 Note, the Tax line is currently not entered for the Orders. Once the Invoices are matched up, it will be. Even if it were, it would be another column on the same line as the Order in the first sheet. This is also all of the information that I can get for this sheet. Note that not only are the VINS duplicated, for this one was are two invoices that are duplicated, and even a couple of moves on the same date But what I have to do (with slightly different order of the columns and a bit other other information, but that is easy), is make it look like this: + A B C D E F 1 VIN Invoice Number Amount Type Invoice Date Order ID 2 WBAVH13538VMRA825 I647898 75 TAX 2026-04-09 170875 3 WBAVH13538VMRA825 I647897 1500 Transport 2026-04-09 170875 4 WBAVH13538VMRA825 I699603 400 Transport 2026-03-12 121139 5 WBAVH13538VMRA825 I699604 20 TAX 2026-03-12 121139 6 WBAVH13538VMRA825 I699603 1800 Transport 2026-03-12 113201 7 WBAVH13538VMRA825 I699604 90 TAX 2026-03-12 113201 8 WBAVH13538VMRA825 I674724 1300 Transport 2026-03-26 149543 9 WBAVH13538VMRA825 I674725 65 TAX 2026-03-26 149543 10 WBAVH13538VMRA825 I647897 400 Transport 2026-04-09 180304 11 WBAVH13538VMRA825 I647898 20 TAX 2026-04-09 180304 So my question is, it make it easier, can I make a drop down in column F of the second table where it is ONLY populated by the Order IDs for the VIN in column A. If it was a different VIN, it would have different order numbers. If course, if someone can come up with a way of matching fully, it would be even better, but between the duplicates, including of the Invoice numbers, and the dates being off, I am not expecting it. I just want to make it a bit easier than copy and pasting the Order Numbers across Table formatting by ExcelToReddit submitted by /u/Ranger7381 [link] [comments]