Fetching multiple records from a excel table.

[HSK]

How to fetch multiple rows of a table from one sheet to another automaticlly based on a criteria?

 

[xld]

Use this array formula as an example


=IF(ISERROR(SMALL(IF(Sheet1!$B$2:$B$20>2,ROW(Sheet1!$B$2:$B$20)),ROW(A1))),"",

INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$2:$B$20>2,ROW(Sheet1!$B$2:$B$20)),ROW(A1))))

 

[HSK]

It did not worked.

Let me be more specific about my requirement. Sheet1 contains a table with field as Sl, Name, Course Attended, Trg Date.

There are about 40 Different names in the Name field. There are about 500 rows in the table.

Now I want to fetch the training record of individual name in separate worksheets each for every name. And, most importantly, I want it to be done automatically. I only need to update sheet1 and other sheets should be updated immediately.


Any help?? (without using MACROS or VBE - I'm scared of these two ghosts.

 

[Hui]

Change your table to a Table, Insert Table

Use a Auto Filter, small drop down on headers, to select your data

 

[HSK]

You are joking dear.

 

[S]

HSK - Probably you could use the advacned Filter, however It would not copy to another sheet.


Also you can have a look @ http://chandoo.org/wp/2010/11/10/vlookup-second-value/


*the Sample File


However, if the number of instances a Name repeats is not constant, the file may become huge, cos of many formula in all the sheets (40).