1. ## 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. ## 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>
<code>
='Week 2'!B37
</code>
and
<code>
=SUM(INDIRECT("'Week 2'!A3:B7"))
</code>
<code>
=SUM('Week 2'!A3:B7)</code>

3. ## 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.

4. ## 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. ## 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. ## Re: Update sheet (2003)

Sorted. Thanks Hans.

7. ## 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. ## Re: Update sheet (2003)

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

9. ## 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. ## 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. ## Re: Update sheet (2003)

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

12. ## Re: Update sheet (2003)

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

Here it is

14. ## 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. ## 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 Last

#### Posting Permissions

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