I want to make a packing slip with macro enabled drop downs in the Item # column.

Put this formula in B20, then copy down. No VBA is needed, is it?

=IF($A20="","",VLOOKUP($A20, List!$A:$B,2,0))

Sorry for the slow response... i was away yesterday.
Thank you for the help... both options work to a point...
one thing further that i need is to include the lines underneath a part number which are included in that part numbers equipment...
there is not always extra lines but only for certain part numbers...
In other words, if a certain part number needs a set of bolts and a sill plate, i want that included in the Packing Slip as well.?
I have attached a spreadsheet to more easily explain what i am speaking about...
2nd Try....xlsm
The green lines in Sheet 1 are single items and have no additional parts...
The red lines have additional parts included, and i have marked those additional lines blue...
So i need exactly what you have done for me, except when i pick a red item number, i need it to pull the red line, as well as all corresponding blue lines for that part number...
Again thanks so much for your help so far...
I added to your rep as that was awesome help...
Hopefully this makes sense...

I don't see the new sheet?

1) On your SHEET1, put blank rows above and below any items that are colored red and multi-rowed items. This will make it easier for the COPY command to just copy the "currentregion" of that item code, the blank rows above and below cut them off from the other items.

2) Then this macro will do the job:

Please Login or Register  to view this content.

I was transfering the "formulas" because you had preformatted your FORM with bands of color. If we start pasting colors that banding would be messed up.

If you're will to just eliminate those bands, then it's a simple tweak:

Please Login or Register  to view this content.

Color bands can be deleted that shouldn't be a problem...
One other thing, when you delete the drop down option, it deletes any preselected text in the adjacent column B,C and D, but not the
"extra parts" if there are any...
So in other words, if i add in a part that has additional parts, then delete that part, i want all the info i just entered to disappear.
If i add a part in A1 that has 2 additional parts... B1,B2,B3 & C1,C2,C3 & D1,D2,D3 all have text copied into them. if i then erase the option in A1, currently only A1, B1, C1, & D1 are emptied...
Let me know if this makes sense...
Also at the same time if you know a way to make Auto Complete on the drop down list so i don't have to scroll through all the part numbers that would be great...
Here is what i have done so far... little changes, but so far working great...
3rd Try....xlsm

Yeah, sorry, I'm not going to do that. Most instances you will add the correct part. When you don't, you can clear all the rows you need to.

My design tip of the day would be that even those child rows that are being added should actually have a "code" that gets added automatically in the first column, too. That would make it obvious to the user those code would need to be deleted as well.


There is no AutoComplete capability in a drop down data validation list. That is a feature of ComboBoxes were you to learn to build an actual FORM, not just a sheet-based form-like tool.

1) Go back to your original list and put in the child codes in the same column.
I would recommend you make sure these codes are unique to each group, a little chaos might ensue if you start using the same code(s) multiple times. The point is to not have the column A values be blank.

2) Updated code:

Please Login or Register  to view this content.

It works fine in my test on your sheet.

http://screencast.com/t/FrJ6TJxuWS0s


I've added a line of code to correct the borders.

Please Login or Register  to view this content.

The picture in p0st #26 shows the existing code is already copying in those blue child lines.

Your application is about automatically transferring row(s) of information immediately upon the selection from a primary drop down list. The page you're referring to on my site is about have secondary drop downs with secondary options based off of the initial selection. It doesn't automatically transfer choices. So, I'd say it's not the same thing.

But you ask if it's usable? Of course, depends on what you change about the way your sheet is to be operated by users.

==============

Yes, I was indicating you should put child part numbers in column A, and that those child part numbers be "unique". Those cells would be copied as well with the updated version from post #24, but I see I forgot to keep that update when i added something in post #26. My bad.

Here is the correct version that includes all:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

No, I just didn't keep it all straight in my head that you want the "formatting" to be kept during to the copying, I don't do this kind of thing at all, typically, so I forget.

One more try, this should do it, and not blow away the validation:

Please Login or Register  to view this content.

Not annoyed at all. I help to learn myself, so it's all good.

If that resolves the question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.