Nov 15, 2013

I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx

The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.

Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:

I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.

I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))

J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))

What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.

Here is the "Area 1" spreadsheet I am working on that has the problem.

First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.

{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$H$3:$H$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(A$6:A6))),"")}

In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.

{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500="Need",ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(E$6:E6))),"")}

Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.

=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(F$6:F7))),"")

It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.

View 3 Replies
View Related