Results 1 to 7 of 7

NFL Spreadsheet stat pasting problem

This is a discussion on NFL Spreadsheet stat pasting problem within the Excel Questions forums, part of the Question Forums category; Trying to auto-update NFL stats (PF) and (PA) on my NFL spreadsheet. __A___B__C____D______E____ 1 115 207 15 =A1+C1 =B1+C2 2 ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    33

    Default NFL Spreadsheet stat pasting problem

    Trying to auto-update NFL stats (PF) and (PA) on my NFL spreadsheet.

    __A___B__C____D______E____
    1 115 207 15 =A1+C1 =B1+C2
    2 148 202 28 =A2+C2 =B2+C1
    |
    |
    (Repeat for other 13 games)

    After the game between teams 1 and 2, A1(PF) and B1(PA) change
    depending on the score of the game (C1 is team 1's score and C2
    is team 2's score). I want to paste special as values columns D and E
    over columns A and B.

    How do I 1) avoid circular reference and 2) clear columns D and E
    (except for the formulas) immediately after the values are pasted to
    columns A to B to prevent the new pasted values from being acted on
    again in columns D and E?

    Of course, maybe there is an easier way to perform these calculations.

    Apprciate any help from the forum.

    Ken

  2. #2
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: NFL Spreadsheet stat pasting problem

    After entering the values in the range A1:C2, and the formulas in the range D1:E2, with the cursor in D1, hold the shift key down and highlight the range D1:E2. Do a Copy (I use Ctrl+c), go to A1, click on Edit, Paste Special, Values, OK. Yes, you don't want D1:E2 to show a new, recaclulated value. My only suggestion is to just don't take them into account. But, if they bother you, why not place the formulas in the range G1:H2, so they will be further away, or in a similar range beyond the normally visible part of the sheet, so they wll be out of sight?

    I could suggest deleting D1:E2 after pasting in A1:B2, but, I assume you wish to use the formulas over and over, so, this would not be an option.

    Please let us know if this helps you out, or if you requiere more help.

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,058

    Default Re: NFL Spreadsheet stat pasting problem

    Quote Originally Posted by kennybak
    Trying to auto-update NFL stats (PF) and (PA) on my NFL spreadsheet.

    __A___B__C____D______E____
    1 115 207 15 =A1+C1 =B1+C2
    2 148 202 28 =A2+C2 =B2+C1
    |
    |
    (Repeat for other 13 games)

    After the game between teams 1 and 2, A1(PF) and B1(PA) change
    depending on the score of the game (C1 is team 1's score and C2
    is team 2's score). I want to paste special as values columns D and E
    over columns A and B.

    How do I 1) avoid circular reference and 2) clear columns D and E
    (except for the formulas) immediately after the values are pasted to
    columns A to B to prevent the new pasted values from being acted on
    again in columns D and E?

    Of course, maybe there is an easier way to perform these calculations.

    Apprciate any help from the forum.

    Ken
    Ken,

    Far fetched idea. What you can do is record a macro that copies D and E paste special D and E and A and B, enter the formula again in D and E.

  4. #4
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: NFL Spreadsheet stat pasting problem

    Brian has hit the labor-saving idea right on the head! Very good!

    Now, as to the other little problem, that you don't want to see the intermediate work-cells D1:E2 change as you update A1:B2, just don't write the formulas in D1:E2 at all, to begin with! Just begin your macro by entering the formulas in D1:E2, then do the Copy of D1:E2, do the Paste Special, Values in A1, and delete D1:E2!

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,058

    Default Re: NFL Spreadsheet stat pasting problem

    Ken,

    A more "prudent" way would be to have a "Score" sheet and "Summary" sheet. Per your example, team 1 and 2 would not play each other every week, but 1 would play 3 and 4 etc. so you'd need to change that layout quite often. You could enter scores in the "Score" sheet and use a Sum or Sumif on the "Summary" sheet. You'd also have a record of the score by each week.

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    33

    Default Re: NFL Spreadsheet stat pasting problem

    RalphA and Brian,

    Thanks for looking at the past unanswered posts and replying to mine. I had gotten discouraged and thought that my subject was either too vague or that it was just not interesting enough to be answered.

    I am updating Week 14 now and will examine the solutions you have provided for updating the PF and PA stats.

    Ken

  7. #7
    Board Regular dandyrandy's Avatar
    Join Date
    May 2002
    Posts
    80

    Default Re: NFL Spreadsheet stat pasting problem

    ******** ******************** ************************************************************************>
    Microsoft Excel - 2002 Regional.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    2
    AmericanWLRARD NationalWLRARD
    3
    West Point (MS) P#212413413 Omaha (NE) P#1322315
    4
    League City (TX) P#5542226-13 Jonesboro (AR) P#212237-2
    5
    Blue Springs (MO) P#4991217-9 Springfield (MO) P#691222-8
    6
    Gonzales (LA) P#8112163 Edmond (OK) P#11112191
    7
    8
    GameDateTeamR RD TeamR RDTimePool
    9
    18/15Gonzales (LA) P#814 -1vs League City (TX) P#5545 110:00aAm
    10
    28/15Omaha (NE) P#14 1vs Jonesboro (AR) P#213 -11:00pNat
    11
    38/15Springfield (MO) P#694 2vs Edmond (OK) P#1112 -25:00pNat
    12
    48/15West Point (MS) P#21210 10vs Blue Springs (MO) P#4990 -108:00pAm
    Scores-Standings

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Take a look at this, I think it shows what you're trying to do.
    I think Brian is describing something similar to this.
    W=Wins L=Loses RA=Runs Allowed RD=Run Differential
    Runs Scored can also be done.
    dandyrandy

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com