Lookup to create List

[raz]

Thanks in Advance:

my data looks like this in columns B to Q

heading1 heading2 heading3 heading4 heading5 heading6 Sales Rep.
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 MNP
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 YRS
blank row
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 YRS
blank row
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 MNP
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 MNP
etc. etc.

On the same sheet,(tab) I need to create a list or organize to print only
for one 'Sales Rep.'
I have drop down list in cell AD3 to select one of the three 'Sales Rep.'
What I need is to have a list (data) copied from the above list, and create
a new list in AA10 and below.

for example if ABC is selected in my dropdown list at AD3 then the list
should look like this.

heading1 heading2 heading3 heading4 heading5 heading6 Sales Rep.
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC
name 1 Company 121 556 50 5463 ABC

 

[Eduardo]

Hi,
Once you select a name from the drop down list, highlight the results, press
CTRL+G, special, visible cells only (is in your right side), then press CTRL
+ C, go to cell AA10, press CTRL + V

 

[raz]

Thanks, I actually Need to do it with lookup.

 

[Dave Peterson]

This sounds like a perfect layout for using Data|filter|Autofilter.

(I'd drop the empty rows, though.)

Select your entire range (don't let excel guess!) and then apply the
autofilter. You can use the dropdown arrows for the sales rep to show the
rows/records associated with the person you choose.