Filter a list and return row to a summary sheet

Hello,

We are having issues creating a fully working VBA code. I included an incomplete code in the attached example workbook but its not working. The goal is to select a name from a drop down list and have a code that will search another sheet for a match and transpose the text back to the "Summary" sheet. As long as the row is not "Completed" or "Cancelled" (we could not figure out how to include that in VBA). Here is the breakdown of the process we are needing:

1. A name is selected in a drop down on G5 in the "Summary" tab
2. Search for that name in the "Data" tab in column K
3. If a match is found then check column Q to make sure its not "Completed" or "Cancelled" (If one of those apply then do not transpose that one row)
4. If neither of those apply (even if its blank in Column Q) then transpose row text only- starting at C9 on "Summary"
5. Then if Person2 is selected, wipe all data starting at C9 and repeat 1-4

Please see the attached as it most likely will be easier to understand. Thank you in advance for any assistance with this. You all are heroes and very appreciated. I have learned so much from this site. Thank you.

Thank you for the reply. I attempted the code in the actual workbook but it keeps returning "Run-time error '1004': Method 'Range' of object '_Worksheet' failed". I assumed the error was coming from those named ranges so I tried to match them 1 for 1 to what you had in the example workbook but that keeps popping up. Could it be something to do with the way that the headers are named? Some have / or # in the actual WB. I copied the headers so they would match but I just cannot seem to get it to work. The "Data" sheet is really a pretty intricate tab full of conditional formatting and such... so that tab will need to change.

I attempted to include the headers from the actual WB into the Example one (you added code in) but it simply does not respond with any data. C9 and below just stay blank if I just change the headers to the ones we are actually using.

Any ideas? Should I just scrap the code we were using?

Thank you for the assistance with this. I very much appreciate it.

Thank you so much for the help. I always forget that you can define sheet names by something like "Sheet1" as opposed to their name "Summary". Makes total sense in retrospect and seeing the difference you included in the code. I also think I could definitely benefit from understanding autofilters, that entire thing confuses me. Made note of all the above though.

Thank you so much for the assistance. I am hoping one day soon I will be on the helping end of this forum as opposed to the question end. Have a great day and Thank You again.