Code to reference multiple areas of another sheet? 1

[Kenja824]

I am creating a database to keep track of a fantasy football pool. I would like to have the rosters on one sheet and on another sheet I would copy and paste boxscores from a website.

My problem is getting formulas to look for the player in the boxscores and return his stats when the stats are in multiple locations. ie.. the players name could appear in the Passing section of the box score as well as the rushing section and even the receiving section.

Another problem is that every week there will be more box scores added and every boxscore will be with two different teams playing each other.

Attached is a file that has only 6 boxscores and a couple weeks of rosters listed.



Skip
You mentioned in a prior thread ....
I'd like to keep one sheet for the entire season for each of those sheets, rather than a sheet per week. So the box scores would need to be labled week by week. Then the rosters would string across the sheet, labeled by week.

Maybe the box score gets pasted into a new sheet each week, but the code would add the scores to a proper table that would have all the extra columns necessary to calculate stats per team per week.

This would be no problem for me but the Boxscores sheet would get quite in depth as 16 boxscores are added to it for each week. I can do it, I just not sure what it would entail in the end when we are in say week 12 and I have to scroll to where the last box score was added or something. But if this would be easier to get the code to work, then it is what it is and I have no problem with that.

 

[Kenja824]

Please keep in mind that every week in this pool, the rosters will be changing as well.

 

[SkipVought]

In what way will the rosters be changing each week?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Just the players who will be listed because each person who joins this pool can only use a player once in a season. So if I have Tom Brady in week-1, I cannot use him again for the season but others may choose to use him if they have not used him yet. If everyone in the league chooses the same QB in a week, that is fine. They just cannot pick him again for the season.

So every week, every roster will have new players that have not been on that roster before.

 

[SkipVought]

Here's your workbook.

The weeks are designated as WEEK 1, WEEK 2...

ADD the next week's box scores until you get all 16 weeks loaded in the first sheet. My process recreates all the tables from scratch for the season each time you run YahooSetup.

I still need to make a procedure to set up a new week. Its a bit messy modifying the formulas

But check it out and let me know.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

2 Procedures to call:

YahooSetup: Takes the box scores by WEEK n and parses the data into section tables. As each week's box scores are added, running YahooSetup reconstructs the Section Tables from scratch.

LoadAllWeekFormulae: Copies the formula from range RosterFormulae to each week for the first team table in each week.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Thanks Skip

Sorry I havent replied. I was out sick yesterday. Which makes me extra busy today. lol I will try to look at it during lunch. Really appreciate the help.

 

[Kenja824]

Hey Skip

Im was just getting into this now and I was wondering. Lets say all of week-1 is done and over and I go to add the WEEK-2 boxscores. Do I leave the week-1 boxscores alone and just continue week-2 below them? Do I replace week-1 with week-2? Do I create a new sheet for BOXSCORE-2?

I will do some testing to see how it works but I just found I need to fix a screw up and am going to be wasting my lunch on it now. lol So I figured I would just ask and see if I get the answer before I get a chance to test it out. )

 

[SkipVought]

The weeks are designated as WEEK 1, WEEK 2...

ADD the next week's box scores until you get all 16 17 weeks loaded in the first sheet. My process recreates all the tables from scratch for the season each time you run YahooSetup.

The order of the weeks is immaterial. But all the weeks' history must be on the first sheet, whatver you want to label it.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Sorry. For some reason my brain read that as All 16 Games. lol You can probably understand why I had to fix my own screwup during lunch. lol One of those days.

Im looking at the code right now and I just found where it says ....

With Sheets(1) 'THIS IS THE YAHOO SHEET
Set rFoundWK = .Columns(1).Find("WEEK", .Cells(.Rows.Count, 1), , xlPart)

 

[Kenja824]

Hey Skip

Not sure if something is wrong or if I did something wrong. I'm attaching the file as it is so you can see what I am talking about. I added all of the boxscores for week 1 and ran the files you said. It seemed to work well, but I decided to change QBs picked and run it again to see what I get. As soon as I pasted Philip Rivers name into the roster slot on a team, the stats automatically updated (which I thought was cool) but it said he had 18 TDs. As I looked closer I see YDS listed for players as 1.68 and such.

A part of me wants to investigate this myself for the learning purpose, but I thought you would be interested to see what went wrong maybe. Depending on how my day goes here, if you find the answer and post it, I may wait to read it until I have a chance to look myself. Just to see if I can figure it out. But if things get to busy for me, I will cheat. Just dont wonder why I dont reply too quick. lol I would be slow figuring something like this out I think. lol If I even can.

 

[SkipVought]

Don't you have Scoring sheet that is intended to factor the raw stats?

Look at the formula and you'll see a factor following the SUMPRODUCT() function.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

!!!! I should have stayed home today. My brain just isnt working right. You are correct. THose are the points value for the stats. Not the stats. lol Sorry.