need help :D

[dc04wrightl]

Thanks for reading this
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

 

[Max]

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 ..

 

[dc04wrightl]

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

 

[dc04wrightl]

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]

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 here:
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

---

 

[dc04wrightl]

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]

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,INDIRECT("'"&B$2&"'!B:B"),0)))
Copy across/fill down to populate the table

 

[dc04wrightl]

hi again
yer i think this is what i mean, im still finding it difficult to
understand, sorry i know im annoying lol
do you think you could have a look at my scoresheet, in the stat tab
i have manualy done the results, although i would realy like it to be done
automaticaly, if you can do that i would be realy amazed lol, thanks for your
help
lyndon

 

[Max]

Upload a sample of your file, post the link to it here

Use either of these free filehosts:
http://www.freefilehosting.net/
http://cjoint.com/index.php

 

[dc04wrightl]

hi, happy new year,

http://www.freefilehosting.net/download/39m5d

ive explained a few things as comments, thanks again, lyndon

 

[Max]

Here's an implemented sample:
http://www.freefilehosting.net/download/39mcb
all_games(1).xls

See sheet: Stats (2),

Input the sheetnames in Q3:X3. Where the sheetname contains an apostrophe,
you need to double the apostrophes, ie input as 2 apostrophes

Example for the sheetname: Westbrook 'A' 1st (with 2 apostrophes)
input it as: Westbrook ''A'' 1st (with both apostrophes doubled up)

Then placed in Q6, copied across/filled down:
=IF(ISNA(MATCH($B6,INDIRECT("'"&Q$3&"'!C6:C16"),0)),"",
INDEX(INDIRECT("'"&Q$3&"'!AF6:AF16"),MATCH($B6,INDIRECT("'"&Q$3&"'!C6:C16"),0)))

The above set-up will enable you to propagate/populate the top left formula
(in Q6)easily across/down without having to manually adjust the sheet
references.

 

[dc04wrightl]

Thanks you so much
Although one more small question
i also need to do the bowling, this never worked for me, i think it might be
something to do with the merged cells. I need the same peoples results,
darren ect. they are in alternative inings to when we batted. i need the
selection from s28 and ab60. as you see these are heavily merged cells, could
this be a problem? i dont mind what you do to it, if it works lol. Thanks for
all the help
lyndon

 

[Max]

Using merged cells do cause problems. Period. But in your case, it's still
possible to get it up working as there is regularity in your sheet set-ups

Here's the implemented sample for bowling:
http://www.freefilehosting.net/download/39mdk
all_games(2).xls

In Stats (2),

Put the label in Q17: Overs

Then placed in Q18, copied across/filled down to X25:
=IF(ISNA(MATCH($B18,INDIRECT("'"&Q$3&"'!C28:C60"),0)),"",INDEX(OFFSET(INDIRECT("'"&Q$3&"'!R28:R60"),,MATCH($Q$17,INDIRECT("'"&Q$3&"'!S27:AD27"),0)),MATCH($B18,INDIRECT("'"&Q$3&"'!C28:C60"),0)))
will return the required results from each source sheet for "Overs" col.

Similar constructs are done for Maidens, Runs & Wickets below Overs. Adapt &
extend to suit ..

 

[dc04wrightl]

Thanks for the reply
Superb so far, although i cant seem to fill in the rest of the tables you
made for overs and so on. the formula seems to still be in there although no
results :S
lyndon

 

[Max]

Welcome, it should work fine. Remember to complete the input of all source
sheetnames in Q3 across when you extend the constructs below. Check also for
data inconsistencies which could throw matching off.

 

[dc04wrightl]

Thanks sorted
the reason it wasnt working is becasue i needed to include the alternative
sheets as they include the bowling
many thanks