Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
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 top 10 with a column for 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))) Anybody have any ideas, thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
Remove the quote around 1
"1" should be 1 (if it a TRUE value) "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 top 10 with a column for 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))) Anybody have any ideas, thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
Ok just did that, and when i removed the quotes from the 1 in the 1st place
cell it worked and gave me the persons name, however when i removed the quotes from the 2 in the 2nd place cell underneith, it still gives me a NUM error??? What am I doing wrong? Thank you "Teethless mama" wrote: Remove the quote around 1 "1" should be 1 (if it a TRUE value) "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 top 10 with a column for 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))) Anybody have any ideas, thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
After you edit your formula make sure you do ctrl+shift+enter, not just enter
"HELPME" wrote: Ok just did that, and when i removed the quotes from the 1 in the 1st place cell it worked and gave me the persons name, however when i removed the quotes from the 2 in the 2nd place cell underneith, it still gives me a NUM error??? What am I doing wrong? Thank you "Teethless mama" wrote: Remove the quote around 1 "1" should be 1 (if it a TRUE value) "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 top 10 with a column for 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))) Anybody have any ideas, thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula
Yep I did that, still not working, this formula was working when the 1-10 was
just a "YES" for finishing in the top 10, then i decided my validation options should be 1-10 instead of just YES, so that it would copy the names over in order of finish, so I thought I could use same formula, just replace "YES" with the numbers 1-10...but its not working, and i did try CTRL+SHFT=ENTER...any other ideas as to what might be wrong. Thanks again. "Teethless mama" wrote: After you edit your formula make sure you do ctrl+shift+enter, not just enter "HELPME" wrote: Ok just did that, and when i removed the quotes from the 1 in the 1st place cell it worked and gave me the persons name, however when i removed the quotes from the 2 in the 2nd place cell underneith, it still gives me a NUM error??? What am I doing wrong? Thank you "Teethless mama" wrote: Remove the quote around 1 "1" should be 1 (if it a TRUE value) "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 top 10 with a column for 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))) Anybody have any ideas, thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |