Coping data from one sheet to other related sheets

[Skidder]

Hi,
Trying to help wife with tenpin bowling admin. Each week a team of 3 individuals plays another team of 3 individuals. I have a results sheet that calcs which individuals won and which team one. All good but to save double entry I want to have Excel copy the individual results for each of the six individuals from the results sheet to their own individual sheet as a new line. The trick is once thats done I want to clear the results sheet and enter results for another 2 teams etc etc
Looked at a Pivot Table and Database techniques but unsure what approach to take...maybe VB script. Any help much appreciated.
Skidder

[Alf]

Try uploading a file this makes it much easier for forum members to help you.

Alf

[Skidder]

Hi All,
It's a work in progress but here is the spreadsheet but assume there are many more individual sheets yet to be added that would get updated from results sheet.

[Alf]

Perhaps a file like this could be of use to you.

I’ve added a sheet called “Template” and I also had to rename the sheet “Nights Results” to “Nights_Results”.

To test run macro “update_player”

Macro checks players name against sheet name. If there is no sheet for a player the macro will add a new sheet and ask you to fill in last year’s average.

After looping through all the records in “Nights_Results” and pasting these to the appropriate sheet all “manual” in date in “Nights_Results” is cleared leaving only the formulas.

I did not understand your calculation using the values in the I column so at the moment this is just copied. Also there were a bit of difference in the way calculations were set up in sheet “Wayne Johnson” compared to that of “Lesley Pain” so I used “Wayne Johnson” for my calculation set up.

Alf

Ps I just saw you have added a new file. I've not checked it yet perhaps you can have a look at the file I'll add and give some feed back on this to see if it's worthwhile to keep on working with it.

[Skidder]

Thanks Alf,
Your macro worked great till I broke it...as it no longer runs in the attached file. I see you removed my lookup of player name (using idividual #) on the night results sheet. I had it there to avoid data entry errors in misspelling a name, so wanted to entry a player number (as per player list)and have vlookup return the correct name and subsequently from that the correct team name. Last part still works.
When the macro runs it should update the individual player sheet with a new dated line with results from their 3 games, the number of games played that night.
The individual sheet formula in colm I was a running games average over the last 7 nights played, which determines their next weeks handicap. Th first few nights of a new year, around 4 from memory, the formula was replaced with last years average since at the beginning of the playing year we dont yet have enough data for the new average to be calc'd.

Are you able to see why the macro is broken??

Really do appreciate your assistance
Bruce

[Alf]

I see you removed my lookup of player name (using idividual #) on the night results sheet

Sorry, bad programming on my part.

I've modified all VLOOKUP formulas so the formula in A4 that was

=VLOOKUP(A4;Players!A:B;2;FALSE)

now reads

Please Login or Register  to view this content.

The ISNA part replaces the #N/A with the more pleasant 0 if there are no values in A4 to A35.

The problem with the uploaded file was that there were no numbers in the range A4 to A35 and this is the range that the macro checks. If no numbers in this range then the macro takes no action.

I was wondering if I should add a sorting loop at the end of the macro. This would sort the player sheets in alphabetical order based on first name. If you think it's useful tell me and I could do it.

When the macro runs it should update the individual player sheet with a new dated line with results from their 3 games, the number of games played that night.

I've checked your last uploaded file and all results found in "Night_Results" are copied to the player’s sheets but not all players have 3 results filled in. Player 3 have none and player 13 only 2.

Regarding the last formula this can be added later when you have enough data to make that meaningful.

Alf

[Skidder]

Thanks again Alf...have downloaded it and will check it out.

As for the players who have less than 3 games or none ......it is real possibility as they may not turn up or have to leave early, so I was exception checking for how the macro would handle this...when it stopped working, but as you explain the macro does nothing if A4 - A35 are blank. The number of games for each night is important to get right, be that 3 or less since this is used on the individual sheet to work out the handicap. Will your macro handle this?

[Alf]

Yes the macro can handle it.

What I do wonder is how about not clearing the “Night_Results” sheet but keep it as a data sheet for say a year.

The macro could then be rewritten to update all player sheets whenever one wanted.

You would then have a “Master data” sheet with all data from each match played at a certain day for the whole year plus all the players’ sheet.

Alf

Ps I'm sending you a private mail as well in order not to clutter up this post.

[Skidder]

Hi,
The macro is overwriting some of the calc fields on the individual sheets. I will PM Alf the details. Latest file attached.

Skidder

[Skidder]

PM doesnt seem to be working....here the oil.....
Hi Alf,
Have been using your macro to update results for the year to date. Noticed a few issues and changes required. Are you able to help?

Can you change the macro to:
Not update/overwrite the following column data: Average, Singles, Total PinFall, Total Games Bowled.....as I have individual sheet formula's to handle this data.
Do update/overwrite the column data: Series
Add a new update to the Points Contributed column.

I'm also wanting to update a Team sheet from the Nightly Results but as a new macro, are you able to create?

It would need to update/overwrite colum data: Date, Games 1,2,3, Series, Team Hcp, Points Won, Points Lost.

Cut down version of the working file is attached in prior post.

Skidder

[Alf]

Hi Skidder

Back after my two weeks holiday so I've started looking at your last request.

I'm still a bit unsure about

Not update/overwrite the following column data: Average, Singles, Total PinFall, Total Games Bowled

so I would like you to confirm which columns that should not be updated on the individual player sheets.

I'm also wanting to update a Team sheet from the Nightly Results but as a new macro, are you able to create?

It would need to update/overwrite colum data: Date, Games 1,2,3, Series, Team Hcp, Points Won, Points Lost.

Yes possible but will should it be a sheet for every team (at the moment you got 6) or should it be only for the "Terrorpins"?

I would also need to know from where on the "Night_Result" sheets these values should be taken i.e. "Team points" from cell I21 and so forth.

At the moment the macro "update_player" clears the "Night_Result" sheet after updating player sheet so one could either incorporate updating the "Team sheet" when running "update_player" macro or remove the clearing command and instead write a new macro for just clearing the "Night_Result" or always update "Team sheet" before running the "update_player" macro.

As you see there are some loose ends that need to be specified to ensure that you get a system that suits your needs.

I have made some changes to the "update_player" macro but not all I think so you need to test to see if I got these changes right.

Alf

[Skidder]

Hi Alf,
On individual players sheet I only need the macro to put data from the night_results sheet into the following colns. Copy Night_Results B2 to Individual sheet Coln row A, equally copy J4 to coln B, E4 to D, F4 to E, G4 to F, I7 to P. And repeat above for each player. All other cells should not be altered on the individual sheets as they have formulae.

For the team update I would like the macro to put data from Night_Results sheet into each of the individual team sheets following colns. So for the first team since Night_Results has 2 teams. (3 players on one team vs 3 players on another team) Copy Night_Results B2 to team sheet A coln, E18 to B coln, F18 to C coln, G18 to D coln, C46 to H coln, C47 to I coln.
For the second team on the Night_Results copy B2 to team sheet A coln, E39 to B coln, F39 to C coln, G39 to D coln, C47 to H coln, C46 to I coln. Please note the switch in cells C46 and C47 for the second team update.
All other cells should not be altered on the individual team sheets as they have formulae.

The sample file I had previously uploaded only contained one individual team sheet as the site would only allow me to upload a small file size. There are in fact 8 team sheets on my master file.

You have a valid point about having a seperate macro since the existing one clears the Night_Results sheet. I think we should just extend the existing macro to do the team sheet updates and then clear the results.
cheersSkidder

[Skidder]

Hi Alf,
Looked at your latest file....macro looks good apart from not yet adding in the points contributed to column P. Hopefully the above discourse explains better the changes and the new requirements.
Thanks for your continued support.
Skidder

[Alf]

Hi Skidder

Hopefully this will fix all your requirements.

The macro now checks if there is a sheet for each team found in the "Nights_Results" sheet. If no sheet exists it will add a sheet with the team’s name.

At the moment the added sheets are organized as follows. First team sheets then the individual player sheets.

Alf

Ps If you do have problems uploading files due to size you could try to zip them before uploading.

[Skidder]

Hi Alf,
New version of macro looks good. I've got more testing to prove it out however I notice on running it the team handicap is not getting updated in column D on the individual team sheet . Sorry looks like an oversight on my behalf. For the first team it comes from Night_Results E19 and E40 for the second team. Are you able to add?
Skidder

[Alf]

Hi Skidder

the team handicap is not getting updated in column D on the individual team sheet . Sorry looks like an oversight on my behalf. For the first team it comes from Night_Results E19 and E40 for the second team. Are you able to add?

No problem doing so but in a pervious post you wanted

Copy Night_Results B2 to team sheet A coln, E18 to B coln, F18 to C coln, G18 to D coln, C46 to H coln, C47 to I coln.

so I'm a bit confused. Should G18 and G39 go to respective D column or should it be E19 and E40?

Alf

[Skidder]

Hi Alf,
you are correct ...sorry. Team handicap in column G (not D) on the team sheet should be updated from Night_results E19 and E40 respectively.
Cheers
Skidder

[Alf]

Hi Skidder

Macro now updated with last info so hopefully all is ok and macro works the way you wanted.

If so could you please mark your thread "solved". You could also click on the small star in my post (bottom left) and give a rating to my efforts.

Cheers

Alf

[Skidder]

Cheers and thanks for all the help......

[Skidder]

Hi Alf,
Thanks to you and the forum for all the good help. I have gone live with the spreadsheet for the TenderFoot Bowling League using the macro. Noticed a bug however I didnt pick up in testing.
Every time the macro is run the second team's sheet isn't found and the macro accordingly creates a new team sheet.

Are you able to correct?
Skidder

[Alf]

Hi Skidder

This is most embarrasing as I thought I had got all the buggs out. Oh well here is the fixed version with no more buggs I hope.

Alf

Ps To add insult to injury this was a stupid mistake of mine. I do not mind so much my clever mistakes but the stupid one! Argh!!

[Skidder]

No worries....I'll check it out....thanks

[Skidder]

Hi Alf,
Thanks for the quick turnaround .....my wife says thank you. Macro now updates the individual player sheets and team sheets....sweet. Many thanks
Skidder