Linking two sheets conditionally

[John Gothard]

I have a work sheet containing a list of players and their rankings. I need
to have multiple other sheets unique to each ranking. Therefore, in the
sheet called 2.5, I want to pull the first and last names of all players
having a 2.5 ranking (but no others). In the master list I have column A =
lastname, B = firstname, C = ranking.
The column will be the same in the 2.5 Ranking sheet, the 3.0 Ranking sheet
etc.

Any help will be great! Thanks
John G

 

[JBeaucaire]

A non-array approach. On your main sheet, add a column D called "Key". In D2
and then copied down, enter this formula:

=C2&"-"&COUNTIF($C$2:C2,C2)+1

You'll see a unique sequential set of numbers appear. All the people with
2.5 will show 2.5-2, 2.5-3, 2.5-4, etc. We skip 2.5-1 because we're going to
start at row2 on the other sheets.

On your sheet for 2.5 ranks, put First and Last labels in A1 an B1. Then, in
A2, put this formula and copy down, and to the right:

=INDEX(Players!A:A,MATCH("1-"&ROW(),Players!$D:$D,0))

That will cause the first match of 2.5-2 to bring over the names, the next
row will match on 2.5-3 and bring them over.

The formula will give an error after it runs out of matches, so you could
test for the match first, like so:

=IF(ISNUMBER(MATCH("1-"&ROW(),Players!$D:$D,0)),
INDEX(Players!A:A,MATCH("1-"&ROW(),Players!$D:$D,0)),"")

Hope that helps.

 

[John Gothard]

I'm feeling a little dumb right now! The first part worked great, all the
"Key" information appeared on my "Players" sheet. The second part however is
where I have the problem. If I put in the formula with checking the result
is all blanks. If I put in the formula without checking I get #N/A in each
First and Last name cell (on the 2.5 sheet). What am I doing wrong?

John G.

 

[JBeaucaire]

Shoot me your workbook and I'll take a look.

jerry AT devstudios DOT com