| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index/large..help please
Hi Group
Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Hi Terry,
Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Thank you kindly Martin.
I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Hi Terry,
Your welcome Terry, The blank column is just a habit of mine. I like keeping things separated for the visual look, it makes for less clutter. Regards Martin "Terry" wrote in message ... Thank you kindly Martin. I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
OK Martin, but I am only a novice senior citizen!!...hence the questions.
I will report to group as I complete my spreadsheet to my desire. Terry "MartinW" wrote in message ... Hi Terry, Your welcome Terry, The blank column is just a habit of mine. I like keeping things separated for the visual look, it makes for less clutter. Regards Martin "Terry" wrote in message ... Thank you kindly Martin. I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Hi
I may have misslead here, because I would like the corresponding columns(in my first post), to move with the Total points, and Team names, if possible? Terry "MartinW" wrote in message ... Hi Terry, Your welcome Terry, The blank column is just a habit of mine. I like keeping things separated for the visual look, it makes for less clutter. Regards Martin "Terry" wrote in message ... Thank you kindly Martin. I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
|
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Hi Terry,
Responded to your Hotmail addy Regards Martin "TERRY" wrote in message ... Thank you Martin.....an email sent to you with the table as requested. . Terry "MartinW" wrote in message ... OK, we must be at cross purposes here. Probably the easiest way to sort it out is for you to send me a copy of your sheet, then I can see what's going on. Send it to Please add an explanation of precisely what you want Also please leave a post here when you have sent it. I get a notification when watched messages are responded to here whereas I would need to keep checking Hotmail on the off chance. Regards Martin "Terry" wrote in message ... Hi I may have misslead here, because I would like the corresponding columns(in my first post), to move with the Total points, and Team names, if possible? Terry "MartinW" wrote in message ... Hi Terry, Your welcome Terry, The blank column is just a habit of mine. I like keeping things separated for the visual look, it makes for less clutter. Regards Martin "Terry" wrote in message ... Thank you kindly Martin. I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
index/large..help please
Cheers Martin.
Terry "MartinW" wrote in message ... Hi Terry, Responded to your Hotmail addy Regards Martin "TERRY" wrote in message ... Thank you Martin.....an email sent to you with the table as requested. . Terry "MartinW" wrote in message ... OK, we must be at cross purposes here. Probably the easiest way to sort it out is for you to send me a copy of your sheet, then I can see what's going on. Send it to Please add an explanation of precisely what you want Also please leave a post here when you have sent it. I get a notification when watched messages are responded to here whereas I would need to keep checking Hotmail on the off chance. Regards Martin "Terry" wrote in message ... Hi I may have misslead here, because I would like the corresponding columns(in my first post), to move with the Total points, and Team names, if possible? Terry "MartinW" wrote in message ... Hi Terry, Your welcome Terry, The blank column is just a habit of mine. I like keeping things separated for the visual look, it makes for less clutter. Regards Martin "Terry" wrote in message ... Thank you kindly Martin. I am trying to work it into my spreadsheet. May I ask what should be in blank "J" column, as you have no mention of it, but include: "I", "K" and "L" columns. Regards Terry "MartinW" wrote in message ... Hi Terry, Try this, you may need to change the ranges. Put this in I4 and drag down to I10 =H4-ROW()/10^10 Put this in K4 and drag down to K10 =INDEX($A$4:$A$10,(MATCH(LARGE($I$4:$I$10,ROW()-3),$I$4:$I$10,0))) Put this in L4 and drag down to L10 =LARGE($H$4:$H$10,ROW()-3) The formula in column I is a helper column and can be hidden. The other formulae can be moved but you will need to adjust the 3 to be one less than the row it resides in. HTH Martin "Terry" wrote in message ... Hi Group Xp Pro M/S Office 2003 I have a single worksheet where I keep records of a bowls league. Headings for the columns(row 3) a Team, Played, Won, Drawn, Lost, +/-, Shot diff', Total points. A4:A10 are the full team names. H4:H10 are the Total points. From above info' is it possible to "automate" this table, when I enter one or more results? I have tried the functions "large" and "index", but unable to get the TEAM NAMES(resulting in "0"), yet I was able to do the Total points column ok!! Currently I manually enter my results as they are available, then I use DATA/SORT to give me the desired table.(highest & lowest scores). TIA Terry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX(LARGE( across worksheets | Excel Worksheet Functions | |||
| LARGE and INDEX functions | Excel Worksheet Functions | |||
| Large Index Match Lookup | Excel Worksheet Functions | |||
| Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
| Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Linear Mode
