• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a way to summarise data from several worksheets into one place

Hi Narayan.


Long story short, this is what I would ideally like (all centering around the Career Stats worksheet):


1.) The bowler name displayed in J1 is currently derived from the Report Filter dropdown list on the BB Figures worksheet. I would like to remove this field altogether, so that the dropdown list in D1 controls the display of all batting, bowling and fielding stats with a single player name selection. However, I cannot currently do that, as the "BB M" and "10wM" fields depend on the BB Figures worksheet's pivot table, which means that I have to select the player concerned both there and in D1. If you can find a way to make the "BB M" and "10wM" fields work from a selection just from D1 (i.e. such that the BB Figures worksheet is no longer necessary), that would be perfect!


2.) I need a better solution for the Team Name in P1 than the data validation dropdown list that I'm currently using. This only allows me to select one team at a time, and ideally I'd like to be able to select multiple teams here (so that I can filter a player's stats either for one specific club that he has played for, all clubs that he has played for, or anything inbetween).


To answer your above questions, if the player name field is blank (assuming that the batting, bowling and fielding stats are now all linked to that single D1 name selection), all the fields can display zeros and dashes. However, this is not really an issue to me, as I am quite happy that this field can never be blank (as is currently the situation using D1's data validation dropdown list). It is no problem to me if the last selected player's stats are always displayed, as I can then just select another player whenever necessary.


Should a player have played for more than one club, it would be perfect if the Team selection cell would then offer a list of all clubs that he has represented (maybe a list of checkboxes?). I can then select which club's performances I want to see displayed, individually or alternatively all of them at once.


3.) This one is a nice-to-have, and not really a priority for me: A custom-made UserForm for data entry, which contains the necessary fields such that completing them in that one place will automatically populate the corresponding fields on the Matches and Indiv Performances worksheets. I was thinking that it'll probably look like a standard cricket scorecard (i.e. with provision for all the usual info of a single innings), such that completing and submitting all the data fields on a per-innings basis will auto populate the relevant rows and columns in the existing format of the Matches and Indiv Performances worksheets. This would then also hold the long-term benefit that I can develop an Excel scorecard for live scoring of matches on the laptop, which would update all the player stats throughout this whole workbook on a ball-by-ball basis (now that's really getting ambitious! I do, however, already have the linear scoresheets that I usually use to score matches on paper in Excel format, so it's not an impossible dream.)


So, that is my Christmas wishlist as far as this workbook goes. Anything that you can help me with will be greatly appreciated!


Thanks so much.
 
Hi Graeme ,


With Christmas so far away , your Christmas wish-list can be longer and more ambitious !


Seriously , I think except for the UserForm , the other requirements are fairly straightforward.


Let me put things down in my own words :


1. You want to maintain the existing worksheets , and the existing data structure within these worksheets.


2. Whatever changes are made will be only in the Career Stats worksheet.


3. You will look at a player's statistics , only one player at a time ; if the player is not a bowler , his bowling figures will remain blank or 0 , as the case may be.


4. The player name selection drop-down will use a manually created unique list of players' names , on the Dropdown Lists Sources tab.


5. If possible , the two selections of Batting Team and Fielding Team will be replaced by only one drop-down selection range , having 5 rows for selection of up to 5 teams.


Can you confirm these points or make the necessary changes , and add any other points you may have ?


Narayan
 
Thanks Narayan - don't tempt me to be more ambitious in my wishes (especially as they impact directly on your time), as the sample file that I uploaded is only part of the full workwook that I am using (I removed some of the other tabs that involve team and individual partnerships, etc.)


To confirm your points above, the answers to 1 - 3 are "yes" (unless you can see a more efficient way of managing the data).


For point 4, the list of player names that appears on the D1 dropdown list actually comes from the Matches Pivot Table. I enter the players from both teams who appear in any match in the Matches workbook as a separate manual action, along with the generic match details. The refreshed Matches pivot table thus gives me the complete list of all players who have appeared in a match involving Cape Town, and I use this to select player names via data validation wherever necessary throughout the rest of the whole workbook (to prevent duplication of records).


For point 5, "yes" is also the overall response. However, I don't think that combining the Batting Team and Fielding Team selections into one dropdown will work at this stage, because the layout of the Indiv Performances tab (where batting, bowling and fielding performances from both sides are all combined into the same rows) requires a differentiation to be made about whether the selected player was in the Batting or the Fielding team at the time (e.g. if you only select the Batting Team for a player, you won't see his bowling and fielding stats on the Career Stats tab, and vice versa). Unless you therefore change the affected formulae used in the Career Stats tab, both headings need to be present - although the same team name should and must automatically appear in both (it's just important that they appear under different headings).
 
Hi guys.


Just checking in after having left this on the backburner for a while. Any progress with the wishlist yet?


Thanks!
 
Hi Graeme,


It was completely slipped out of my mind....I am really very very sorry...


Now a days I am little busy with my work, but I will get back to you as soon as I can..


Thank you for your patience dear..


Kaushik
 
Hi Narayan.


There still appear to be issues with the HS1 and HS2 career stats for the batting, and the BBI, BBM and 10wM stats with the bowling, as these are team-dependent for players who have appeared both for and against Cape Town.


See the dropbox link for the updated Sample 5 workbook, which now includes five seasons' worth of data to give more comparison options: https://www.dropbox.com/s/55srfuj2x6f8286/Sample%205.xlsm


Note particularly the batting career stats of a player such as Lance F Bleekers, who played both for Cape Town as well as against them for Langa. If I delete the Batting Team indication in P1 in order to get his combined Cape Town / Langa career record (note that his HS1 is 61* for Langa, but only 58 for Cape Town), the "Selected_Team" in AB2 changes to "*", but this then screws up the team-specific portion of the formulae for the HS1 and HS2.


Similarly, on the bowling front. Another player to look at is Mark J Barham, who played both for Cape Town as well as against them for Claremont. His BBI for Cape Town was 2-15, while purely for Claremont it was 4-79.


There are also plenty of other players to choose from who played both for and against Cape Town, and even more who played against Cape Town for more than one opposition club.


I'm not sure how you got the pivot tables to reflect the player selected on the Career Stats tab automatically, or how the named ranges that you gave to their Report Filters fit in, but maybe the multiple team selection option is a better way to go considering the above issues?
 
You must log in or register to reply here.
Back
Top