Need a way to summarise data from several worksheets in one place on another worksheet

[Grumpy88]

Hi.

I have created a fairly large Excel "database" that consists of several worksheets, each containing a different type of data about the same people. There is also a fair degree of overlap between the worksheets due to generic info that appears on all of the worksheets (i.e. the same columns containing the same info), with the golden thread running between all of the worksheets being the same people's names.

My present set-up is that I use data validation dropdown lists to enter the generic info on each worksheet, and enter the data specific to each individual worksheet's purpose manually on each sheet. All of the data on each worksheet is linked to the same names (although not every name appears always appears on every worksheet). I then have a separate pivot table for each worksheet, to summarise that worksheet's data according to name or any of the other generic info.

This method works, but is probably not the most efficient (either in terms of entering the data initially or summarising it afterwards). I can live with the data entering method (although using one form as a single point of entry that would automatically populate all of the necessary columns on the various worksheets with just a single data entry action would obviously be far better), but ideally what I am really interested in now is to have a single data summary display that
would show me all of the data from each worksheet for a selected person in one place - rather than having to use three or four different pivot tables in turn to obtain this data progressively.

My idea was thus to open a new worksheet that would display the overall summarised data from each worksheet in one place for a given selected person. I thus created a data validation dropdown list on this new worksheet to serve as the tool for selecting the person whose data I want to view, and then added a number of other columns next to that, each designed to display a specific data type obtained from one of the other worksheets.

Therein lies the problem for me though, as I've been unable to find a formula for these columns that will look for the selected name in all of the other worksheets, identify the relevant data for that person, and sum / count the data as a summary in the appropriate column on the new overall summarised data worksheet. I thought that COUNTIFS might work, with the criterion being the selected person's name, but I don't know how to establish the link between the name on the overall summarised data worksheet and the same name on each of the other worksheets.

Any ideas please? Thanks!

[arlu1201]

A couple of formulae should do it for you, but before that, do you have a sample file that you can upload?

[Grumpy88]

Sorry - I should have done that first. A attaching a cut-down sample workbookSample.xlsm.

[Grumpy88]

Has no-one got a solution for me yet?

I see that the title of my post is a little misleading. All I am really looking for is an explanation of how I can use a dropdown list on one worksheet to select a name, such that Excel identifies all the rows in another worksheet that contain that name, and counts / adds together data from specified columns in those identified rows.

Can this be done by formula, or is VBA necessary?

Thanks.

[Fotis1991]

Hi

If i understand well, then take a look to my sample.

No VBA, 2 validation list and simple countif+countifs functions.

So in Columns A & B i put Validations list. In Column C, this formula.

Please Login or Register  to view this content.

I did not see well the others columns but i believe it will be the same way.

If you have more problems, let us know.

[Grumpy88]

Thank you very much for your response, Fotis1991. I really appreciate your time and effort.

I've actually been playing around myself with formulas in the meantime, and have managed to solve quite a few of them using DCOUNTA, DSUM, DMAX and similar database-based formulas. I'm actually feeling quite chuffed with myself!

However, I am struggling with the correct formulas to get columns P and Q (as they appear in the Sample spreadsheet) of the Summary Data worksheet to function properly. This is because I got the formula in column O to work by adding a new column C between the existing columns B and C. To this new column C I added the heading "Score" in cell C1 and the requirement ">=100" in C2. I then used a DCOUNT formula in cell O2 (which with the insertion of the new column C is now actually cell P2), whereby the Criteria is the formula is A1:C2 of the "Summary Data" worksheet. That correctly picks up all scores of 100 or more made by the player selected from the dropdown list in cell A2.

Unfortunately though, the flip side of that is that I can't do the same sort of thing for the formula needed for P2 (now Q2), because the DCOUNT Criteria seems to need an uninterrupted range of contiguous columns, and as C2 is already now in that range and specifying only scores of 100 or larger, it is interfering with the >=50 and <100 requirements for Q2 (I can't just add a new column D with that requirement in cell D2 as I did for the new column C, as the Criteria range A1:D2 for my DCOUNT formula would then include two conflicting requirements - in C2 and D2).

Any ideas on getting around this (the formula needed for the original cell Q2 (now R2) will pose similar problems, as its requirements are scores of 0, where the "How Out" column indicates "not out")?

Thanks!

[Grumpy88]

Okay.

I've managed to get formulas to work for all but two of the cells in the "Career Summary" worksheet - K7 and R7 (see attached updated spreadsheet). Can somebody please help me with formulas for those two cells please?

Cell K7 must find all rows in the "Indiv Performances" worksheet that match the name selected in B3 of the "Career Summary" worksheet. It must then compare all the matches in which that player bowled (each match has its own ID, in column E of the "Indiv Performances" worksheet), pick up in which indivdual match that player took the most wickets (which will be one or two entries per match, as indicated in column AH of the "Indiv Performances" worksheet), and return the highest number. In addition, the corresponding number of runs conceded (as in column AG) should that number in the same row(s) as those of the most wickets must be returned on the other side of the hyphen in cell K7, and should the highest number of wickets taken in a match occur more than once, then the instance with the least number of corresponding runs conceded must be displayed here.

Cell R7 must count the wickets taken by the player in each individual match, and return the number of times that ten or more wickets were taken in the same match.

Anyone fancy their ability?

Thanks!

[Grumpy88]

Sorry - my sample spreadsheet was marginally beyond the 1MB upload limit, and I didn't have time to fix it.

I have now done so - please see attached.

Thanks.Sample 2.xlsm

[Grumpy88]

Hi.

Just a bit more clarifcation on how the two formulas in K7 and R7 should work (for those unfamiliar with cricket):

Essentially what the formula in R7 must do is the following:
- Find all rows in the "Indiv Performances" worksheet which have, in column AC, the same name that was selected in cell B3 of the "Career Summary" worksheet.

- In all such identified rows, it must then add the data in column AH that share the same entry in column E (there will be either one or two rows per separate column E entry).

- It must then indicate the number of times (i.e. per separate column E entry) that this sum totalled ten or more.

Perhaps an example will help: Say in Match #5 (the column E entry in the "Indiv Performances" worksheet) a player takes 5-57 (i.e. a "5" in column AH and a "57" in column AG) in one innings (row), and 6-18 in the other.

The formula should thus detect that these two rows represent returns in the same match (they share the same entry in column E), so it should add the two column AH entries and check whether they total ten or more. In this case they did (5+6=11), so cell R7 of the "Career Summary" worksheet should dislay "1" - indicating that this player has taken ten or more wickets in a single match once in his career. Should he do it again in a subsequent match, cell R7 should then return the value "2", etc.

The formula that I need for cell K7 is unfortunately even more complicated. It needs to follow the same steps described above for the formula in R7, but then do the following as well:

- It must then return the highest value of the column AH sum as the first part of its display, followed by a hyphen.

- The second part of the display, after the hyphen, must display the summed totals of column AG for the same row(s) selected.

- In addition, where there is more than instance of the same result being returned re the highest number of wickets taken in a match (i.e. the column AH sum returned above), then the second part of the display must return the lowest value of the sum of the column AG row entries concerned.

Continuing the above example, therefore, cell K7 of the "Career Summary" worksheet should firstly display "11". In addition, it must then place a hyphen after the "11", and then display the sum of the corresponding column AG entries thereafter (i.e. "75"). This indicates that the best bowling performance by that player in a match was "11-75", i.e. 11 wickets taken for 75 runs conceded.

However, if the player in a later match again took eleven wickets (for example 4-16 in one row, and 7-35 in another, again both sharing the same entry in column E for the player concerned), then the formula in K7 must obviously do the same calculations again, but realise that the return of "11-51" in the second match is better than the "11-75" first achieved (he only conceded 51 runs instead of 75 runs the second time around, for the same number of wickets taken). The formula in K7 must the change from "11-75" to "11-51", to reflect his career-best bowling performance in a full match.

Can anyone conjure this up?