Excel Filtered Data

Please upload a sample workbook.

Attached is an example. My form that I need to show the filtered data is the sheet called Master Shipping Ticket.
QC-Log-Blank.xlsx

Will there be any other description? apart from Widget & Thingy? or this is just example?

That's just an example. There will be multiple descriptions. The part numbers, qty's, and descriptions will all be unique.

Thank you

I have created a named Range for the ID codes which will expand as products are entered to the main list. There is a Data Validation drop down to select the ID on the form which is then used by VLOOKUP to populate the other data
QC-Log-Blank.xlsx

That doesn't accomplish what I'm needing. I've attached another spreadsheet with some actual parts in it. I have the first sheet filtered by 2 different columns. What is showing after being filtered is what I need to insert into my form (the shipping ticket sheet).

Thank you
QC-Log-Blank.xlsx

So you want to filter the list and show the results in the form, you will only be able to do that using VBA. I can't see why the example that I posted doesn't meet your requirements, all you do is select the required IDs to populate the form

I see where you're going now. I can make that work, except, the quantity and part number columns on the form are reversed. Also, when I select something from the drop down, it doesn't populate the rest of the cells in that row.

Actually it does populate the other cells but not correctly.

Hi Turk,

Please find attached...
You Select the Description in JobItem Sheet at B1 and click Update Ticket Button, It will automatically copy the Qty, Part No & Description to Shipping Ticket Sheet at A16....

Hope it helps.
QC-Log-Blank_3.xlsm

let me know what needs populating, but all you need to do is amend the VLOOKUP formulas.

If you want a VBA solution then try this
QC-Log-Blank-v1.00-.xlsm

Roy,

Is your file something different from my version? :)

The only problem I have in my version, if user needs to select multiple description, then both of our code will not work.

The code is totally different! My code uses the ListObject's inbuilt parts.

Your data validation does not seem to work for the drop down.

In my opinion your code uses variables for ranges unnecessarily.

'Clear the Shipping Ticket
Set ST_Form = ST.Range("A16:I36")
ST_Form.ClearContents

Select allOpen in new window

Compare

'Clear the Shipping Ticket
ST.Range("A16:I36").ClearContents

Select allOpen in new window

You also use With Statements unnecessarily

With ST
    .Range("B16").PasteSpecial Paste:=xlPasteValues 'Part No.
End With

Select allOpen in new window

The above code could simply be.

 ST  .Range("B16").PasteSpecial Paste:=xlPasteValues 'Part No.

Select allOpen in new window

You use SpecialCells with no provision for errors  caused if no cells are found. Also, it is totally unnecessary because Excel will only copy visible cells.

The author does not say whether he wants  to select more than one item. If this is the case then AutoFilter can only handle two criteria so I would use a different method. I actually do not see any advantage in using VBA instead of formulas.

Yes, I need more than one item. My plan is to filter the Job Item sheet by the QC'd column. Then the filtered data is what needs to go to the shipping ticket. There will always be multiple items and different descriptions that will need to be transferred to the shipping ticket.

What would be the maximum number of  items  selected? Two can be handled by AutoFilter.

What about my original suggestion with the formula approach? It will be a simple matter to edit the formulas for you.

It could range from 1 item to 30 items.

Any way to make it work would be fine with me. I'm just trying to get it so that I wouldn't have to keep copying and pasting.


Thank you.

I think the formula method is the best myself. What is actually wrong with my first suggestion?

Nothing but the qty and part # are reversed on the shipping ticket. Also, in your original file, it only fills out one line.

Or, am I missing something?

I'll check . You simply need to change the formula to refer to the relevant column and copy down.

Thank you, I'll make that work.

Thank you

Pleased to help. If you need help adapting it further then post back.