Michaelson
New Member
- Joined
- Aug 14, 2011
- Messages
- 14
Windows XP, Excel 2007.
I'm currently working on a statistical database for the Australian National Rugby League competition for my own purposes. This is the first time I've worked on something in Excel this involved and was hoping for a few pointers. I've put my specific queries in bold though if that's more what the forum is for.
So this is how my database is structured:
First I recorded a bunch of data from games this season recorded like so
Next I generated pivot tables in a new worksheet
Finally and most importantly I'm looking to extract the data from the pivot tables and to fill out fill out the columns in worksheet #3 (partially pictured):
In the pivot tables worksheet the columns '2H For' and '2H Against' I added manually, as that data is not explicitly recorded in the data worksheet. I think this is probably a clumsy way to do things, but it's the best I know at this point. I wonder if I should generate/arrange this data differently? I'm particularly curious if I can integrate these statistics into the pivot table at all? I may have to do this with other statistics later on as well.
Secondly, and my main problem, up to this point I have been manually entering functions into every cell in worksheet #3. Most of the functions are simple, but they can get a bit fiddly and it is a looooong process.
Is there a way that I can take the formula for one team's statistic and use it to fill out the corresponding statistics for other teams with ease? If I click the box in the lower right-hand corner of one cell and drag it across it just generates the same result in every cell. What I'm after is a similar function but where Excel adjusts the formula so that team specific statistics are generated. Is this possible? On top of being time consuming to enter the formula individually for every cell, it greatly increases the likelihood of careless mistakes creeping in to the spreadsheet as well.
As I say though, any general tips to make easier/improve this spreadsheet would be greatly appreciated as well! Some general Excel techniques I should look into perhaps? Common pitfalls for this sort of database I should try to avoid?
Many thanks in advance for any replies
I'm currently working on a statistical database for the Australian National Rugby League competition for my own purposes. This is the first time I've worked on something in Excel this involved and was hoping for a few pointers. I've put my specific queries in bold though if that's more what the forum is for.
So this is how my database is structured:
First I recorded a bunch of data from games this season recorded like so
Next I generated pivot tables in a new worksheet
Finally and most importantly I'm looking to extract the data from the pivot tables and to fill out fill out the columns in worksheet #3 (partially pictured):
In the pivot tables worksheet the columns '2H For' and '2H Against' I added manually, as that data is not explicitly recorded in the data worksheet. I think this is probably a clumsy way to do things, but it's the best I know at this point. I wonder if I should generate/arrange this data differently? I'm particularly curious if I can integrate these statistics into the pivot table at all? I may have to do this with other statistics later on as well.
Secondly, and my main problem, up to this point I have been manually entering functions into every cell in worksheet #3. Most of the functions are simple, but they can get a bit fiddly and it is a looooong process.
Is there a way that I can take the formula for one team's statistic and use it to fill out the corresponding statistics for other teams with ease? If I click the box in the lower right-hand corner of one cell and drag it across it just generates the same result in every cell. What I'm after is a similar function but where Excel adjusts the formula so that team specific statistics are generated. Is this possible? On top of being time consuming to enter the formula individually for every cell, it greatly increases the likelihood of careless mistakes creeping in to the spreadsheet as well.
As I say though, any general tips to make easier/improve this spreadsheet would be greatly appreciated as well! Some general Excel techniques I should look into perhaps? Common pitfalls for this sort of database I should try to avoid?
Many thanks in advance for any replies
