INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Code to reference multiple areas of another sheet?

Code to reference multiple areas of another sheet?

(OP)
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.

RE: Code to reference multiple areas of another sheet?

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

RE: Code to reference multiple areas of another sheet?

In what way will the rosters be changing each week?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to reference multiple areas of another sheet?

(OP)
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.

RE: Code to reference multiple areas of another sheet?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to reference multiple areas of another sheet?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to reference multiple areas of another sheet?

(OP)
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.

RE: Code to reference multiple areas of another sheet?

(OP)
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. clown

RE: Code to reference multiple areas of another sheet?

Quote:


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to reference multiple areas of another sheet?

(OP)
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)

RE: Code to reference multiple areas of another sheet?

(OP)
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.

RE: Code to reference multiple areas of another sheet?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code to reference multiple areas of another sheet?

(OP)
!!!! 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close