Table/List Sum Formula ?

First post. Thanks for this opportunity to the webmaster and administrators here.

I am hoping to find a solution for the following problem in Excel 2007:

First, hope these photos are easy enough to see. They are also attatched if not. 2nd, I hope this is possible

Table in the first image is created from data in the data sheet pictured in the second image. Both tabs are in the same spreadsheet. Each number in the list in column D of the first image is a sum of U from the separate data sheet tab for the particular player (player is C in this sheet, E in the data sheet).

\1

The green and white block areas of the pic below (going down the sheet) are top 15 finishes in PGA golf events (tournaments). Each block contains 15 names and associated tournament data. This is the data entry area of the data sheet. Most of this is hidden, because it is not related to the problem. To the right is generated data. The farthest right columns, X and Y, are running sums of the number found in U (a tournament specific generated number). They are calculated using only data from the previous 8 golf tournaments (120 entries overall), in the case of X (Mojo Last 8), and only the previous 225 entries, in the case of Y (Mojo Last 15). Then the current event is added in for a total of 16 events. The total span is 135 rows for the X data and 240 rows for the Y data.

\1

The latest entry of X and of Y are the highest for their respective ranges. I need to create a table like in image one, which contains each player (column E of the data sheet) represented in the range of the bottom 135 entries of the data sheet, each player listed one time (Formula 1 needed). The list will be placed in C2 just like in pic one but in tab 2 (see below for tab names). Then I need a formula to return the sum of the U column data in the data sheet (image 2) over the 135 lines from the bottom of the column up for each specific player represented in that range (the ones in the player list for the range created by formula 1). The number is already there in the data sheet in the X column, so what I think could be best would be to create the list of players in the range (this would be the first column of the list and placed in C2 down of tab 2). Then find the latest X for each player in the list in the range of the bottom 135 of the data sheet and return that number for each player represented to the table next to the player's name. I don't know how to do this, and I am having no luck. The database of data shown in pic 2 gets larger throughout the year with the addition of each tournament, so I cannot set specific ranges for the last 135 rows for the table. I just either have to find the last X in the datasheet over the last 135 rows for each player or sum up the Us from the bottom for the player over the last 135 rows.

Here is the formula for the data in the table in picture 1. The span there for calculating the number was the whole spreadsheet of data in the data sheet, 18-962. The players' names are all the players represented in the range. It sums U for each E and returns the total (to column D in image 1) next to the player's name (column C in image 1) in sheet 4:

=SUMIF(DataSheet!E$18:E$962,C2,DataSheet!U$18:U$962)

Basically, I need something similar but that either:

1. Sums the Us for the bottom 135 lines of the U column in the data sheet (pic 2) for each player

or

2. Returns the first column X value starting from the bottom of the sheet in the data sheet (pic 2) for each player over the bottom 135 lines. Again, the result will be compiled in tab 2.

Data Sheet is called: DataSheet
tab 4 is called: Top 15ers YTD
tab 2 is called: Top 15 Last 8
Spreadsheet is called: mojo_masters_2(1)

I am beside myself about this, so I would appreciate any help whatsoever. Thankyou!

Welcome to the forum. Please note that cross-posting (posting your issue on multiple sites without a link to the other sites) is against our rules. The reason is that it can waste our time if someone else has already provided a solution. http://www.mrexcel.com/forum/showthr...GA+Golf+Events

Also, please post a workbook, rather than pics. We don't want to redo your work to test our solutions and there's always the possibility your workbook has something in it that prevents our proposed solution from working, yet that wouldn't show up in a picture.

We don't "stand for" being bulletin board police. We also have no problem with you posting your question on multiple sites (though many of the gurus and mods are active on more than one site anyway); what we have a problem with is not telling us you've done so and posting a link. We enjoy helping, but we also want our contributions respected; when you crosspost without links, you make it likely that some of us will put in our time helping you when you already have your solution and we could be employing that time to help others. Nobody likes having their time wasted, even if that time was willingly volunteered.

As for forums that enforce their rules, I find that this one does a good job of it without the moderators being over the top or rude. Some of the others have less of an issue with breaking their rules; some have more. I hope you find a forum that suits you.