Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP
Ok this is for tournament results, I have my first page with about 70 rows of
players, each row has their name, address, tournament number, etc. My last column is a pull down cell (validation) with numbers 1-10 representing 1st - 10th place finish. The tournament director will only pull down and select 1st through 10th for only those people who finish in top 10. Ok on second page I have a table for those who finish in the top 10 with a column for their name and another for amount of money earned for that finish in order from 1st to 10th place. What I need is a formula that will search the first page for only those with a number (1-10) in the finish column and then transfer that name to the second page and put them in order 1-10th place to match the order of my prize money. This is what i was trying to use, but am having problems with it: =INDEX(REGISTRATION!A$15:A$94,SMALL(IF(REGISTRATIO N!I$15:I$94="1",ROW(A$15:A$94)-MIN(ROW(A$15:A$94))+1),ROWS($1:1))) I have already tried it with and without quotes around the number and I remembered to CTRL+SHFT+ENTER for this Anybody have any ideas, thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP
Try this (normally entered, not an array):
=INDEX(REGISTRATION!A$15:A$94,MATCH(SMALL(REGISTRA TION!I$15:I$94,ROWS($1:1)),REGISTRATION!I$15:I$94, 0)) Copy down Biff "HELPME" wrote in message ... Ok this is for tournament results, I have my first page with about 70 rows of players, each row has their name, address, tournament number, etc. My last column is a pull down cell (validation) with numbers 1-10 representing 1st - 10th place finish. The tournament director will only pull down and select 1st through 10th for only those people who finish in top 10. Ok on second page I have a table for those who finish in the top 10 with a column for their name and another for amount of money earned for that finish in order from 1st to 10th place. What I need is a formula that will search the first page for only those with a number (1-10) in the finish column and then transfer that name to the second page and put them in order 1-10th place to match the order of my prize money. This is what i was trying to use, but am having problems with it: =INDEX(REGISTRATION!A$15:A$94,SMALL(IF(REGISTRATIO N!I$15:I$94="1",ROW(A$15:A$94)-MIN(ROW(A$15:A$94))+1),ROWS($1:1))) I have already tried it with and without quotes around the number and I remembered to CTRL+SHFT+ENTER for this Anybody have any ideas, thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP
Sounds like a simple HLOOKUP or VLOOKUP formula should work. Did you already
try those? "HELPME" wrote: Ok this is for tournament results, I have my first page with about 70 rows of players, each row has their name, address, tournament number, etc. My last column is a pull down cell (validation) with numbers 1-10 representing 1st - 10th place finish. The tournament director will only pull down and select 1st through 10th for only those people who finish in top 10. Ok on second page I have a table for those who finish in the top 10 with a column for their name and another for amount of money earned for that finish in order from 1st to 10th place. What I need is a formula that will search the first page for only those with a number (1-10) in the finish column and then transfer that name to the second page and put them in order 1-10th place to match the order of my prize money. This is what i was trying to use, but am having problems with it: =INDEX(REGISTRATION!A$15:A$94,SMALL(IF(REGISTRATIO N!I$15:I$94="1",ROW(A$15:A$94)-MIN(ROW(A$15:A$94))+1),ROWS($1:1))) I have already tried it with and without quotes around the number and I remembered to CTRL+SHFT+ENTER for this Anybody have any ideas, thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP
Another way.
If you would add the price to the number in your list All you would need to do is a sort on the first page. sort by the last column. Greetings from New Zealand "HELPME" wrote in message ... Ok this is for tournament results, I have my first page with about 70 rows of players, each row has their name, address, tournament number, etc. My last column is a pull down cell (validation) with numbers 1-10 representing 1st - 10th place finish. The tournament director will only pull down and select 1st through 10th for only those people who finish in top 10. Ok on second page I have a table for those who finish in the top 10 with a column for their name and another for amount of money earned for that finish in order from 1st to 10th place. What I need is a formula that will search the first page for only those with a number (1-10) in the finish column and then transfer that name to the second page and put them in order 1-10th place to match the order of my prize money. This is what i was trying to use, but am having problems with it: =INDEX(REGISTRATION!A$15:A$94,SMALL(IF(REGISTRATIO N!I$15:I$94="1",ROW(A$15:A$94)-MIN(ROW(A$15:A$94))+1),ROWS($1:1))) I have already tried it with and without quotes around the number and I remembered to CTRL+SHFT+ENTER for this Anybody have any ideas, thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|