Create sublist from larger list

I am writing a training program in Excel and fumbling my way through it. Here's the scenario---someone who knows a little more about excel told me that, in order to do what I want, I need to create a macro (which I have never done). Someone else told me it could be done but it would leave a lot of blank spaces-----

Lets say that column A has "Demonstrates knowledge of CAD System" and columns B-K each represent a separate day in training---showing a trainee's progression from 1 (the lowest) to 5 (the highest). Column L is going to show the average of B-K. There are, for example, 30 separate items in column A. This all appears on 1 sheet of a workbook. Okay, the tough part. If the trainee scores less than a 3 (showing in column L) as the overall average of one of the 30 items from column A, I want the "Demonstrates knowledge of CAD System" (for example) to automatically appear on a separate sheet under the title AREAS OF IMPROVEMENT. In other words, the program would, on a separate sheet, highlight for the trainer/trainee what areas need to be corrected, rather than them having to scour 5 or 6 sheets looking for scores below a 3.

Under the AREAS OF IMPROVEMENT, I don't want a whole bunch of blank spaces to appear, just the text from sheet 1, column A3,A9, or any other category where the score was below 3.

So, any assistance you guys could provide would be greatly appreciated. Thanks!!


EDIT: And whatever the solution is, I would prefer for it to be as automated as possible. Other people, who know even less than I do, are going to be using the program. I want them to just be able to punch in a trainee's score and not have to depend upon knowing how to perform functions in excel.

Something like this oughta do it....

=IF(ROWS($A$1:$A1)>COUNTIF(Sheet1!$L$2:$L$100,"<3"),"",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$L$2:$L$100<3,ROW(Sheet1!$L$2:$L$100)-ROW(Sheet1!$L$2)+1),ROWS($A$1:$A1))))

Note: Adjust the ranges to suit... don't touch the ROWS($A$1:$A1) part, though.

Then you will confirm the formula with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear around the formula.

Then you will copy the formula down the column as far as you want.

I've explained it here before... check it out...

http://www.excelforum.com/showthread...18#post1833718

So, if I wanted to place that formula on sheet 7, and I wanted it to contain information from sheets 1-6, would I have to repeat the parts where you listed "Sheet 1" or could or can I do it over consecutive sheets in an easier way? I don't know if what I just wrote was clear...sorry.
Also, would the formula that you provided would only have to be entered into one cell on sheet 7----even if, for example, 10 different AREAS OF IMPROVEMENT were needed and would be listed.
EDIT: On sheet 7 would I just create a huge cell that would encompass all of the data pulled from the other sheets, or would I have to repeat and modify the code into several different cells?

The formula is meant for only 1 sheet...

you can perhaps create headers on Sheet 7, one for each Sheet (1-6) and apply the formula in those columns... each column would represent a separate sheet.... so all you would replace is the sheet name in the formula.

The formula is entered in one cell and then confirmed with the CSE keys... then you would drag it down to fill as many rows as you want....