Populating entire rows of data from a master sheet?

[Agrajag]

I'm in a fantasy football league I'm in that's extremely numbers-based
Lots of data analysis to do (fictional players so it's year round wit
several seasons each year).

The league provides a massive CSV file that contains all data for al
players in the entire league. This is a large-ish file with 2700 player
and all their attribute data (name, position, height, weight, strength
intelligence, speed, etc.) There are literally dozens of attributes an
all of them appear in the master CSV even though each position only use
a subset of them (a Center has a "Snapping" attribute (his ability t
snap the ball to the quarterback) while a Running Back would have n
such value there as he never does that.)

The main issue is to then have sheets for each position to make viewin
and analysis simpler. Plus, this data changes often so the goal is t
just be able to re-populate the master sheet as needed and hav
everything else auto-populate. Nothing will happen on the master sheet
It's just a placeholder of all the data.

I'd then have a sheet for each unique position type. One for a Q
(quarterback) and that would need to look at the master sheet and fin
all entries where Position = QB and copy those rows to the QB sheet.

However, remember that each position only uses a subset of the overal
attributes so I wouldn't want to grab the full row. I'd only want to ge
specific parts of the row relevant to each position ignoring anythin
unneeded.

Then, lastly, on those position-based sheets that's where all m
calculations will be. Players are rated in each attribute on a scale o
0-99 and my approach gives players a "bonus" for being above a certai
number for each attribute but a heavy "penalty" for being below tha
number.

As an example, take an offensive lineman. Strength is a key attribut
for them. To be a good lineman you must have a strength of at least 80
My calculation for Strength for linemen might be 1 and 10. In othe
words, for every point above 80 you get a point but for every poin
below 80 you lose 10 points.

The point of the above is to make it clear that each positional shee
will have lots of formulas and such on it so there's more to conside
than just dumping data from one sheet to another. The target sheet als
needs to be able to preserve the formulas that are there.

How can I automate this?

Thanks...

 

[Agrajag]

With great difficulty would be my initial answer...

Sounds like something more suited to Access perhaps...

However, Anything is possible given enough time to work it out, s
perhaps if you post a copy of your workbook so far that might help.

The file seems to be too big to upload. It'd be pretty hard to get i
down to 90k from 21MB

 

[Dorian74]

The file seems to be too big to upload. It'd be pretty hard to get i
down to 90k from 21MB.

one of the options is to use a realy looooooong if formula.

=if(a1="qb",a1,if(a1="second postion","second postion",if(a1="3r
position", 3rd position, if(...

another option is to do this semi-manually:
1)highlight the 1st row (where all the row titles are"
2)press the "filter" button. You can find it on the tool bar unde
the"sort & filter" button.
3) now you can press on the little arrow in the 1st line in the row o
positions and mark only the position you are intrested in (let's sa
"qb")
4)highlight the whole spreadsheet and "copy"
5) go to a different spreadsheet (named "QB") and just paste
6)repeat for every position and every time the "Master Spreadsheet" i
updated

Cheer up it's not as much work as it sound

 

[WallyWallWhackr]

I'm in a fantasy football league I'm in that's extremely numbers-based.
Lots of data analysis to do (fictional players so it's year round with
several seasons each year).

The league provides a massive CSV file that contains all data for all
players in the entire league. This is a large-ish file with 2700 players
and all their attribute data (name, position, height, weight, strength,
intelligence, speed, etc.) There are literally dozens of attributes and
all of them appear in the master CSV even though each position only uses
a subset of them (a Center has a "Snapping" attribute (his ability to
snap the ball to the quarterback) while a Running Back would have no
such value there as he never does that.)

The main issue is to then have sheets for each position to make viewing
and analysis simpler. Plus, this data changes often so the goal is to
just be able to re-populate the master sheet as needed and have
everything else auto-populate. Nothing will happen on the master sheet.
It's just a placeholder of all the data.

I'd then have a sheet for each unique position type. One for a QB
(quarterback) and that would need to look at the master sheet and find
all entries where Position = QB and copy those rows to the QB sheet.

However, remember that each position only uses a subset of the overall
attributes so I wouldn't want to grab the full row. I'd only want to get
specific parts of the row relevant to each position ignoring anything
unneeded.

Then, lastly, on those position-based sheets that's where all my
calculations will be. Players are rated in each attribute on a scale of
0-99 and my approach gives players a "bonus" for being above a certain
number for each attribute but a heavy "penalty" for being below that
number.

As an example, take an offensive lineman. Strength is a key attribute
for them. To be a good lineman you must have a strength of at least 80.
My calculation for Strength for linemen might be 1 and 10. In other
words, for every point above 80 you get a point but for every point
below 80 you lose 10 points.

The point of the above is to make it clear that each positional sheet
will have lots of formulas and such on it so there's more to consider
than just dumping data from one sheet to another. The target sheet also
needs to be able to preserve the formulas that are there.

How can I automate this?

Thanks....

This is not exactly what you are after, but does carry many of the
elements, and has the benefit of being about football, AND has a good set
of team helmets to use.

http://office.microsoft.com/en-us/t...or-tracking-the-year-s-games-TC101939767.aspx

The 2009 version is better because I think it has the data filled
in,but if you fill in the game stats, the green and red zones all work
right.

Took me a few iterations to get it somewhat right. Haven't worked on
it since they messed with the template site.