Transposing Info or Pivot Table???

[Cmorgan]

Evening,

I'm not sure of the name/technique I am after, but essentially it is where you have a drop down arrow and you can select a name and then it shows only the data that relates to this.

So in my case the first sheet has all the data for a whole class of students (the values are 1,2 or 3, and this then conditional formats the colour of the cell).
On the second sheet I would like to have the 'arrow box' in cell B1. You select the student name in it and it drags all the info for just that student and it puts it in the sheet.

Any help you can give would be great.

Thanks

[martindwilson]

like this maybe
like this maybe
oops i got a 3 for
3L I do not repeat myself!

[Cmorgan]

Many thanks for this, it works perfectly. Can you briefly explain how you created this, or what it is called and then I will research via google how to do it.

Many thanks

[martindwilson]

it just uses index /match see example 2 here
INDEX / MATCH -- Example 2
http://www.contextures.com/xlFunctions03.html#intro
and a named range for the dropdown(look in name manager for that,but i believe you could just refer directly to the range in excel 2010)

[Cmorgan]

Many thanks