| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help :D
Thanks for reading this :D
ive made, and been using a cricket score sheet for some time now on excel. for those who dont know all i do is write down names of playes and there runs they score and so on. it was all going fine untill i was asked to add up all the totals in games. basicaly, if there is one i need a general formula or a way of adding up numbers on different sheets. although here is the twist, when i made a table for the players names i realised that i need some way of telling it to search for the name in the seperate sheets. becasue in cricket the batting and bowling line up can change. so basicaly i need a way to search for the name and then to add up runs which is next to it i hope this is clear thanks lyndon |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
One guess is that your summary set-up could probably use a SUMIF, possibly
with INDIRECT inside to read row/col labels. The row labels could be the player names, and the col labels could be your sheetnames As a first pitch here ... try this sample from my archives: http://savefile.com/files/414328 Interactive Summary.xls Adapt it to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dc04wrightl" wrote: Thanks for reading this :D ive made, and been using a cricket score sheet for some time now on excel. for those who dont know all i do is write down names of playes and there runs they score and so on. it was all going fine untill i was asked to add up all the totals in games. basicaly, if there is one i need a general formula or a way of adding up numbers on different sheets. although here is the twist, when i made a table for the players names i realised that i need some way of telling it to search for the name in the seperate sheets. becasue in cricket the batting and bowling line up can change. so basicaly i need a way to search for the name and then to add up runs which is next to it i hope this is clear thanks lyndon |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
hi thanks for the reply
im struggling to understand the template, it has the concept of my idea, perhaps i need to explain more basicaly i need to make a table of results, and it would be nice if i could get excel to fill it self in auto if the table was on 1 worksheet and had a name and result on another worksheet. say darren in b2 and his score in cricket 53 in c2 pete in b3 and his score 32 in c3 the problem is that batting order changes in cricket and pete might be first in the table, is there a way i can order this thanks for the reply lyndon "Max" wrote: One guess is that your summary set-up could probably use a SUMIF, possibly with INDIRECT inside to read row/col labels. The row labels could be the player names, and the col labels could be your sheetnames As a first pitch here ... try this sample from my archives: http://savefile.com/files/414328 Interactive Summary.xls Adapt it to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dc04wrightl" wrote: Thanks for reading this :D ive made, and been using a cricket score sheet for some time now on excel. for those who dont know all i do is write down names of playes and there runs they score and so on. it was all going fine untill i was asked to add up all the totals in games. basicaly, if there is one i need a general formula or a way of adding up numbers on different sheets. although here is the twist, when i made a table for the players names i realised that i need some way of telling it to search for the name in the seperate sheets. becasue in cricket the batting and bowling line up can change. so basicaly i need a way to search for the name and then to add up runs which is next to it i hope this is clear thanks lyndon |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
perhaps this will help, its a very basic version of what im on about
all i need to do is complete the table and was wondering if i could tell excel to search for the speciflyed name and collect data. thanks lyndon "Max" wrote: One guess is that your summary set-up could probably use a SUMIF, possibly with INDIRECT inside to read row/col labels. The row labels could be the player names, and the col labels could be your sheetnames As a first pitch here ... try this sample from my archives: http://savefile.com/files/414328 Interactive Summary.xls Adapt it to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dc04wrightl" wrote: Thanks for reading this :D ive made, and been using a cricket score sheet for some time now on excel. for those who dont know all i do is write down names of playes and there runs they score and so on. it was all going fine untill i was asked to add up all the totals in games. basicaly, if there is one i need a general formula or a way of adding up numbers on different sheets. although here is the twist, when i made a table for the players names i realised that i need some way of telling it to search for the name in the seperate sheets. becasue in cricket the batting and bowling line up can change. so basicaly i need a way to search for the name and then to add up runs which is next to it i hope this is clear thanks lyndon |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
Based on your lines:
.. need to make a table of results, .. if the table was on 1 worksheet .. and had a name and result on another worksheet. say darren in b2 and his score in cricket 53 in c2 pete in b3 and his score 32 in c3 Perhaps INDEX/MATCH might provide a way out for you I've framed up a simple example he http://www.freefilehosting.net/download/39kh9 Index n Match Example.xls You should also check out Debra Dalgleish's nice coverage on INDEX/MATCH at her: http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
hi thanks for the reply
it is working although it is taking a long time to set up, on 18 sheets it has the same layout is there a way of setting up the table quicker? "Max" wrote: Based on your lines: .. need to make a table of results, .. if the table was on 1 worksheet .. and had a name and result on another worksheet. say darren in b2 and his score in cricket 53 in c2 pete in b3 and his score 32 in c3 Perhaps INDEX/MATCH might provide a way out for you I've framed up a simple example he http://www.freefilehosting.net/download/39kh9 Index n Match Example.xls You should also check out Debra Dalgleish's nice coverage on INDEX/MATCH at her: http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help :D
Perhaps you meant something like this:
http://www.freefilehosting.net/download/39l4j Index_n_Match_Example2.xls In the summary sheet, With source sheetnames listed in B2 across, player names in A3 down Put in B3: =IF(ISNA(MATCH($A3,INDIRECT("'"&B$2&"'!B:B"),0))," ",INDEX(INDIRECT("'"&B$2&"'!C:C"),MATCH($A3,INDIRE CT("'"&B$2&"'!B:B"),0))) Copy across/fill down to populate the table -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dc04wrightl" wrote: hi thanks for the reply it is working although it is taking a long time to set up, on 18 sheets it has the same layout is there a way of setting up the table quicker? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
