Hi, I have downloaded the NFL Excel schedule in pivot table format from
Microsoft's website. Here's my query.

I want to extend the table so that you can enter in user's choices as
to who will win each game and then a column (or other method) to select
which team actually won. I'd like to keep a running tally of each
user's correct choices throughout the season.

I can use data validation lists to match winners and losers, but can't
figure out how to copy the validation formatting consecutively down the
columns so that each game is displayed correctly. Perhaps the answer
lies within expanding the pivot table itself, which I know very little
about, unfortunately.

I can email the document in the event someone would like to take poke
at the challenge of helping me set this up...Thank you for your help.

Jacob

YOu could add columns on the Weekly Data sheet. For example, in column
I, enter your pick, in column J, enter the team that won, and in column
K, use a formula to keep track of your record. For example:

=IF(I2=J2,1,0)

Add a heading to each column, e.g. Pick, Won, Points.

Then, select the pivot table, and on the PivotTable toolbar, choose
PivotTablePivotTable Wizard
Click the Back button
Change the data range to include the new columns:
'Weekly Data'!$A$1:$K$257
Click Finish

On the NFL Schedule by Week sheet, unhide the hidden columns
Then, drag the Points field to the data area
You can show the Grand Total for Columns to see the number of correct picks.

jacob wrote:
Hi, I have downloaded the NFL Excel schedule in pivot table format from
Microsoft's website. Here's my query.

I want to extend the table so that you can enter in user's choices as
to who will win each game and then a column (or other method) to select
which team actually won. I'd like to keep a running tally of each
user's correct choices throughout the season.

I can use data validation lists to match winners and losers, but can't
figure out how to copy the validation formatting consecutively down the
columns so that each game is displayed correctly. Perhaps the answer
lies within expanding the pivot table itself, which I know very little
about, unfortunately.

I can email the document in the event someone would like to take poke
at the challenge of helping me set this up...Thank you for your help.

Jacob



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi!

I have no idea what the Microsoft NFL Excel schedule in pivot table format
looks like but if it's a pivot table I automatically hate it ! <g

I have a file that does what you want. Want a copy?

It's all setup for the upcoming season.

Biff

"jacob" wrote in message
ups.com...
Hi, I have downloaded the NFL Excel schedule in pivot table format from
Microsoft's website. Here's my query.

I want to extend the table so that you can enter in user's choices as
to who will win each game and then a column (or other method) to select
which team actually won. I'd like to keep a running tally of each
user's correct choices throughout the season.

I can use data validation lists to match winners and losers, but can't
figure out how to copy the validation formatting consecutively down the
columns so that each game is displayed correctly. Perhaps the answer
lies within expanding the pivot table itself, which I know very little
about, unfortunately.

I can email the document in the event someone would like to take poke
at the challenge of helping me set this up...Thank you for your help.

Jacob

Yes, please Biff.

File on it's way.

Biff

"jacob" wrote in message
oups.com...
Yes, please Biff.

Debra,

Thanks for the quick reply. This is what I've found. I put in the above
instructions but it doesn't work. I picked the first 2 games correctly,
resulting in "1", netting me a grand total of 2 points. Then i entered
an incorrect pick, netting a zero under points on the Weekly Data
Sheet, but on the pivot table it showed a "1" under the newly created
points column, giving me 3 points, which isn't correct. What did I do
wrong?

Also, is there any simple way to create the I column under Weekly Data
sheet a choice between the two teams instead of typing it in each time?
Thanks!!

The points are being counted, instead of summed. To change this:

Right-click on the Points heading in the pivot table,
and choose Field Settings.
From the Summarize By list, select Sum
Click OK

To create a dropdown list of choices, you can use data validation:

Select cells I2:J257
Choose DataValidation
From the Allow dropdown, select List
In the Source box, type: =$B2:$C2
Click OK

Now, select any cell in that range, and it will have a dropdown list
with the team names.

jacob wrote:
Debra,

Thanks for the quick reply. This is what I've found. I put in the above
instructions but it doesn't work. I picked the first 2 games correctly,
resulting in "1", netting me a grand total of 2 points. Then i entered
an incorrect pick, netting a zero under points on the Weekly Data
Sheet, but on the pivot table it showed a "1" under the newly created
points column, giving me 3 points, which isn't correct. What did I do
wrong?

Also, is there any simple way to create the I column under Weekly Data
sheet a choice between the two teams instead of typing it in each time?
Thanks!!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html