Want to fill multiple rows and columns by selecting drop down

[rpcamill]

I have never done VBA before, and i cant do this with just functions on excel.

Id like to select a drop down and have it fill in a table that has that value.
Example:
The drop down selection id like to do is: Tester type "120VAC" which is in my "BOM" tab.
when i select this id like it to look in my parts table, which is in my "tables" tab to find everything that has "120VAC" in the lookup column.
I would like it to fill out a table with those 16 rows with with 5 columns back on my "BOM" tab.

I am not sure if this is possible, and any help would be greatly appreciated!

[modytrane]

See attached file.
I have entered formulas down to row 50.

Here's a typical formula in A14

It's an array formula, entered with ctrl+shft+ent.

Hope this works for you.

[FDibbins]

If you could swap the QTY and Price/Unit around so both tables match, this would be *bit* simpler, using this ARRAY formula (no VBA needed)...
=IFERROR(INDEX(Tables!B:B,SMALL(IF(Tables!$A$2:$A$100=BOM!$B$2,ROW(Tables!$A$2:$A$100)),ROWS($A$1:A1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across, then down as needed

[modytrane]

Both solutions are same. in either case, to make the last column look neater, you may want to change that formula to:

in F14 and copy down.

[FDibbins]

Both solutions are same. in either case, to make the last column look neater, you may want to change that formula to:

in F14 and copy down.

Indeed they are, and posted at the same time, too

I agree with an addition to neaten up the last column, but I would just add =IF(A14="","",......................)

(again the same thing)

[Logit]

.
With dropdown :

First fill in I1:I5 as shown in image. Then, click in B2 / Select DATA TAB, then DATA VALIDATION / Data Validation (again) / complete form as indicated.

Data Validation.jpg

[FDibbins]

Logit, can you show your formula so we dont need to open your file, please?

[rpcamill]

Wow, i have a lot to learn! Thank you very much! Ill play with that and see what i can come up with.

One last question and i should be on my way.
Can i select the next drop down and make that add to the list if i added a 10A in the lookup column?

[FDibbins]

With that formula whatever is entered into Bom B2, will be searched for in Tables. If it exists, it will bring it/them back - if no, it will show "nothing"

Give a try and see where you get

[rpcamill]

Is there a way to make the next drop down in b3 add to the list? I tried to change the array to make it look in that cell, and a couple of other things with no such luck. I changed the lookup in the tables tab to have a couple "10A" and i selected the 10A from b3 nothing happened or it would just get 1 number.

[FDibbins]

Can you show an example of what you mean/want?
(I have a feeling you are talking about cascading dropdowns)

[Logit]

.
This macro is pasted into a ROUTINE MODULE :

Please Login or Register  to view this content.

This macro is pasted into the BOM SHEET LEVEL MODULE :

Please Login or Register  to view this content.

[Logit]

.
Improved version :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[rpcamill]

thank you logit!!
Fdibbins, basically I am trying to make a whole bill of materials in the "BOM" tab by selecting different drop downs. So eventually id like to select all of those drop-downs and fill out that list. So when I select the b3 cell to 20A it would fill out more of that list after I select the "120VAC", all of the way down to computer drop down.

[Logit]

.
So if you first select 120vac and the BOM is populated with approx. 16 rows of data ... do you then want to be able to select say 230VAC and add that data to the bottom of what has already been written
to the BOM ? Or, is the workbook as presently setup in Post #13 sufficient ?

[rpcamill]

Id like to only select one from that drop box. So id select, lets say the 120VAC and it will fill the list like it does with whatever says 120VAC in the table tab. Then I would go to the next drop box and select either 10A or 20A and it would add to that list with whatever I have in the table marked 10A or 20A and keep going down the drop downs until they all have a selection

[Logit]

.
I'm curious of the ultimate goal for your project and wonder if perhaps we are headed in the wrong direction ?

The reason I ask is because usually when a BOM list is created, it won't include everything of a particular category. It usually contains several different items
within a specific category. So, instead of copying and pasting all 120VAC items form the TABLES sheet to the BOM sheet, you would want to select just a few.

Do you really intend to copy all 120VAC items to be pasted into the BOM sheet ? And, all of the say 20A items to be pasted below the 120VAC items ?

[rpcamill]

This is for quoting purposes. So id like for someone who knows nothing about the parts or any of that, to be able to select an option of build and it would generate a semi complete BOM for them. We have standard items that go with each section of category, and they really don't know what goes with what. So i am trying to make a configurable BOM for them to get a general price on the equipment.

That is why i am trying to add items for each selection after the first one. I know it sounds complicated and im even confused myself haha.

So yes i do want to add the 20A items below the 120 or whatever was selected

[Logit]

.
i've looked at your project several different ways. I believe the best approach will be for you to go the FORMULA ROUTE. It is cleaner ... requires less coding ... and will most likely be faster retrieving the data for the BOM.

Check with FDibbins or modytrane for their assistance with the formulas.

Cheers.

[rpcamill]

okay thank you for your help anyway!

[JeteMc]

I believe that you could accomplish what you want by adding a column to the 'Tables' sheet that would populate using a formula that would search for a match between the lookup value in column A and any of the drop downs in column B on the BOM sheet. The formula might look like: =ISNUMBER(MATCH(A2,BOM!$B$2:$B$9,0))
You could then populate the five columns on the BOM sheet based on a value of TRUE in the added column.
1) The formula for the Part Number column could be:

Please Login or Register  to view this content.

2) The formula for the other columns would be similar to:

Please Login or Register  to view this content.

3) Zero values are hidden using Conditional Formatting.
Let us know if you have any questions.

[rpcamill]

Thank you JeteMc, that works perfectly!

One last thing I am trying to implement is hours.
Basically, id like to do one of the selections and it would add Controls hours and build hours for a sum B13 and B14.
I added a new table, but i'm thinking it may be better to add two columns to the parts table.
I was thinking of putting the hours just on one of the parts for that selection, adding up the hours and putting it in the total hours box. I would then hide the column those hours are in on the BOM.

Is this the best way to go about this, or do you have a better way of going about this.

[FDibbins]

Thanks for the feedback

[JeteMc]

Given that the hours will be in their own table the formulas could be:
For B13: =SUMIFS(Hours[Controls Hours],Hours[Column1],TRUE)
For B14: =SUMIFS(Hours[Build Hours],Hours[Column1],TRUE)
If each part has its own control and build hours then you could add hours columns to the part_list table on the Table sheet. I feel as if you would not need to add additional columns to the BOM sheet, however it might be helpful to see an example before saying anything definite.
Let us know if you have any questions.