Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update sheet (2003)

    I have a excel workbook with 3 sheets in it, called Total Stats, Week 1, Week 2. Cells in Total Stats contain the contents of cells in Week 1, and Week 2. The problem I have, is that the information in Week 2, comes from an Access report. I have to update Total Stats by running the report every two weeks to do a comparison. Week 2, will (or should), becomes week 1. And another sheet inserted, which I rename Week 2. Doing it this way loses all references in Total Stats. How can I maintain the references, so when I delete the old sheet, and rename the new, the Total Stats still works. There must be a better way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    One way would be to use INDIRECT in the formulas on the Total Stats sheet. For example:
    <code>
    =INDIRECT("'Week 2'!B37")
    </code>
    instead of
    <code>
    ='Week 2'!B37
    </code>
    and
    <code>
    =SUM(INDIRECT("'Week 2'!A3:B7"))
    </code>
    instead of
    <code>
    =SUM('Week 2'!A3:B7)</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update sheet (2003)

    Another "Jippo" method!

    Hans's is less effort...but for the sake of options...here goes....

    1. On the Total Stats, run a replace command. Replace all = with # (or any other out of ordinary symbol)
    2. Delete Week 1 and Rename Week 2 to Week 1. Insert another sheet, rename to Week 2
    3. Go to Total Stats and replace all # with =.

    This updates and recalculates the new figures.
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    Thanks to you both. Hans' suggestion seems to be working, but I've come up with another problem with a formula. In cell B7 I have 39%, in C7 I have 60%. In cell D7, I have the formula =IF(C7>B7,"DOWN",IF(C7<B7,"UP",H7)). H7 states if C7=B7 then "NO CHANGE". On this occasion, I would expect the result to say "UP", but it says "DOWN". Most of the cells give the right results, there just seems to be a few like this one that give the wrong results.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    It's probably a matter of rounding errors. Try something like this:

    =IF(ROUND(C7-B7,9)=0,H7,IF(C7>B7,"DOWN","UP"))

    This says: if C7 and B7 are equal up to the 9th decimal, return H7, otherwise check if C7>B7.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    Sorted. Thanks Hans.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    Not sorted. After I've updated and renamed Week2 to Week1, I get a #REF error in the H column.

    =IF(E6>0,"NO CHANGE"&" ON OUTSTANDING " & #REF!D6,"NO CHANGE, ALL DONE")

    E6 seems ok (which in this case is 9). Do I need to put indirect in front of D6?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    Yes. See <post:=537,357>post 537,357</post:>.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    I'm still getting problems with the main formula. I have :

    =IF(ROUND(C27-B27,9)=0,H27,IF(B27>C27,"UP","DOWN"))

    as you suggested. Some of the results are right while others are wrong.

    eg, B27 is 33% and C27 is 50% yet the reslut is showing "DOWN"

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    Apparently you supplied an incorrect formula in <post:=538,448>post 538,448</post:>. Exchange "UP" and "DOWN" in the formula.

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    I've tried reversing them. That just reverses the result. The ones that were right are now wrong

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    Could you post a small sample workbook that contains both correct and incorrect results?

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    Here it is

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update sheet (2003)

    The values in column E in the Week2 worksheet are text, not numbers (as you can see if you look at the error indicators in the upper left corner), so the comparison is incorrect.
    - Select an empty cell.
    - Press Ctrl+C to copy it to the clipboard.
    - Select Week2!E3:E27.
    - Select Edit | Paste Special..., Add option, click OK.
    This will convert the text values to numbers.

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update sheet (2003)

    Thanks Hans. The cells are being sent from Access as Text, even though the report is formated as a percentage. I think I need to put in a macro to do a you suggest, to save the user having to do it.

Page 1 of 2 12 LastLast

Posting Permissions

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