Transferring a value assigned to a name in a separate sheet

**bobbby1949** · 09-13-2010, 03:55 PM

I'm not sure how I can accomplish this, but here is what I want to do.

I have a sheet (Sheet1) that has a column of points (D) assigned to a particular name (A). The sheet is in workbook 1. I will be saving each workbook for ten different events (Event 1, 2, 3, - 10).

I want to add another sheet to each workbook that records the points assigned to each name, for all ten events separately. It would be a copy of the same sheet. This sheet (Sheet2) will have a name column (A) and ten points columns (B, C, D, E, F, G, H, I, J, & K) accounting for each of the weekly events. This sheet will accumulate the weekly points and determine the standings. Week 1 (B) will be filled in with the points from workbook 1. Week 2 (C) will be filled in with points from workbook 2. Etc. The Standings sheet will eventually be completed in Workbook 10.

I have successfully designed the "Standings" sheet to handle all the calculations needed. What I need to find out is how to copy the points in each workbook into the Standings sheet and assigned to the correct name. The names will not always match the same row number, so I need a code that identifies the cell to copy from Sheet 1 in Workbook 1 to the standings sheet that will be in Workbook 1, Then do the same thing the next week in Workbook 2 from Sheet 1. And so on.

I'm puzzled how to make this simple. The Standings sheet must stay, even if I have to keep it in a separate workbook and enter the points manually each week. I was hoping that the I could have the points automatically filled from each weeks events though.

Any help is greatly appreciated.

**JBeaucaire** · 09-13-2010, 04:20 PM

Just post up your workbook showing your data sheet(s) and then a manually mocked up STANDINGS sheet where you've done it all by hand. Make it easy for us to see the end result desired. We'll take a shot at replacing your manually inserted numbers with formulas that accomplish the same thing.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**bobbby1949** · 09-13-2010, 05:31 PM

Attached are the two sheets I am working with.

The "Scorecards" sheet provides the origin data that I want to copy into the "Point Standings" sheet.

The data in AB needs to be copied to B.

There will be different data for the next nine "Scorecards" every week that will be copied into C thru K of the "Point Standings" sheet.

Remember, each week will be a different workbook file saved.

**JBeaucaire** · 09-13-2010, 07:43 PM

I'll look at what you've uploaded, but nothing about the solution offered will come from "remembering", I'm going by the sample data alone. If you have some sort of multi-workbook need, then I would expect multi-workbook sample data and sample results based on the multi-workbooks samples you have provided.

**JBeaucaire** · 09-13-2010, 07:55 PM

1) I fixed the STANDINGS sheet so that the names are represented the exact same way as your score card. Have the be consistent.

2) Then a simple INDEX/MATCH formula does the work...this in B6:
INDEX(Score1101!$AB:$AB, MATCH($A6, Score1101!$B:$B, 0))

In this final version I've uploaded, I added some error checking so you don't #N/A for the people who don't participate in a particular week.

=IF(ISNUMBER(MATCH($A6, Score1101!$B:$B, 0)), INDEX(Score1101!$AB:$AB, MATCH($A6, Score1101!$B:$B, 0)), "")

3) I would suggest you simply put each scorecard into this workbook as a separate sheet, then edit the formula you put into C6 to point to the new card, then copy down. Easy to adjust.

**bobbby1949** · 10-01-2010, 03:03 PM

Jerry, please forgive me for taking so long to get back. Frankly, I was confused with the corrected sheets you made. I finally had some time to examine it closer and I believe the code you provided will do the job in my work book.

However, I should point out that the scorecard sheet is only one of six data sheets (now seven with the standings sheet) in the work book. Much of the data in the scorecard sheet comes from a pairings sheet. Also, data in the scorecard is transferred to three other sheets, recap, results, and pools sheets. There is another sheet, registration, that provides all the sign up data, including monies.

I mention this because I would rather add the point standings sheet to the current book. Save each book as a event file (weekly events), and start over fresh the next week.

With that said, I am hoping that updating the point standings sheet to each week using the event number, will have a sheet showing the current point standings.

Make sense to you?

Thanks very much for your help. The code you provided does what I was looking for.

**JBeaucaire** · 10-01-2010, 03:30 PM

Originally Posted by bobbby1949

Make sense to you?

No I can't say I follow any of that through a verbal description.

**bobbby1949** · 10-01-2010, 03:50 PM

Attached is the workbook. I am happy with what I have so far. You will see the point standings sheet included.

I want to save as each weekly event to file. Start over next week, update the point standings sheet, save as to file again, and so on.

I will end the season with ten files. Events 1, 2, 3, etc.

I use the same workbook for other events where I don't need to use the point standings. So after week 10, the point standings is irrelevant.

**JBeaucaire** · 10-01-2010, 06:15 PM

Once you've completed a week, you can highlight the values in that POINT STANDINGS column and copy > edit > paste special > values to flatten the results. With the formulas gone from that column, you can put those formulas into the net column and change the data on the PAIRINGS and put new data on the SCORECARDS to get a new column of data for the next week.

Repeat this process each week. Using Separate workbooks for a cumulative project seems unnecessary.