Search a range based on a cell value and have specific columns of that related data copied

I have a large sheet of data with a form on another sheet with a list box.

I need to click on the list box (select a name) and have that name searched on the large sheet.
Find all the rows relating to that name but only copy 4 colums to the form not the entire row.
I have tried INDEX & MATCH but that only looks for one row

I have also done an Advanced filter but cant get it to output only the required columns.

If I click on the Drop List on my FORMS sheet, the name must be looked up on the MAIN DATA sheet and then gather the relevant info and put it in the Column Headings on the FORM sheet.

Many thanks

Penny

Sample.xls

Hi,

It sounds like you should be using the standard Excel filtering functionality, either the AutoFilter or the Data Advanced Filter. Check out that functionality.

If necessary upload your workbook in which you have manually created a set of the results that you expect to see for any given name selection.

Hi Richard

I have uploaded my sheet, You are correct in Autofilter and Advanced Auofilter but need it to run straight from the drop box. This has me stumped.

Hi

I have done this vba to use your suggestion, but it copies the entire row. How can i get it only to copy say 4 columns of info.

Many thanks

Penny

Thulani Zulu is not in your Original sheet.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

Please Login or Register  to view this content.

I don't see a new file (with new information) added in your post.

I have a large sheet of data with a form on another sheet with a list box.

I need to click on the list box (select a name) and have that name searched on the large sheet.
Find all the rows relating to that name but only copy 4 colums to the form not the entire row.
I have tried INDEX & MATCH but that only looks for one row

I have also done an Advanced filter but cant get it to output only the required columns.

If I click on the Drop List on my FORMS sheet, the name must be looked up on the MAIN DATA sheet and then gather the relevant info and put it in the Column Headings on the FORM sheet.

Many thanks

Penny

Sample.xls

With a helpcell and index / match