Create sublist from larger list

[writeguy37]

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.

[NBVC]

Something like this oughta do it....

Please Login or Register  to view this content.

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.

[writeguy37]

Wow...no way I would've figured that out by noodling around.

I hate to be a pest, but can you try to explain exactly what is happening there---what logical steps are being taken to make that calculation. Thank you so much!

[NBVC]

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

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

[writeguy37]

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?

[NBVC]

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....

[writeguy37]

OKay, I just threw together a quick sheet and it worked after I spread the formula out to several cells.

=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))))


In this formula, if I wanted to indicate that I needed the formula from, say, sheets 1-3, how would I do it? I didn't quite follow your last posts directions (through no fault of your own----I am a total noob).

[NBVC]

You would create a new formula in the next column replacing the Sheet1 with Sheet2..

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

and again in the next column for Sheet3.

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


so you would have 3 separate columns representing extracts from 3 separate sheets.

[writeguy37]

First, thanks so much for your help!!!!!!!!!!!

Since the AREAS FOR IMPROVEMENT (despite the info coming from 6-7 separate sheets) is going to be a relatively small list, is there a way---using your formula---for each item to appear in the same column rather than, as you mentioned in your last post, in adjoining cells?

[NBVC]

Well, if you insist on having it that way, then probably best to get someone to write you a macro that will go through each sheet and extract the values to one column on your summary sheet...

[writeguy37]

No that's okay....
I thought of a way that I could apply it to my needs.
Thanks again, you've been a great help