Auditing Toolbar Conundrum

[isidey]

I have a complex and large (4.4MB) workbook I am editing in Excel 2004
for Mac, v11.1 (040909), using OS X v10.4.2 on a powerbook 1.5GHz, 1GB
DDR SDRAM.

The auditing toolbar is behaving eratically. On the same, unprotected
sheet, the "Trace Precedents" works fine in all cells, but "Trace
Dependants" works for some cells, but not for others (it indicates no
dependants even when there are dependants).

I have repaired permissions, and deleted the com.microsoft.excel.plist
and com.microsoft.excel.prefs.plist files with Excel closed, then
re-opened Excel. No change. If I re-do a formula in a cell, then the
"Trace Dependants" works fine; however, this is not a good option, as I
have several redundant areas I need to erase to clean it up, and I need
confidence that the "Trace Dependants" tool operates at all times to
ensure I do not wipe out anything that is still connected to the
ooutput of the edited workbook.

Any assistance would be much appreciated.

 

[Bob Greenblatt]

I do not know what is happening. But a pretty safe way to "redo" the
formulas would be to select the area and then do an Edit replace, replacing
"=" with "=". This will force the formula to be reentered. If this cleans it
up great. If not, let us know.

 

[isidey]

Bob, Many thanks for your rapid reply.

Neat trick, and it worked for about 80% of the recalcitrant formulae.
However, I still have the problem with some formulae, so I basically
still have the same problem.

Any other possible neat tricks?

 

[Bob Greenblatt]

Well, you could try replacing the "=" with something really unique, like
"XXX" and then replacing the "XXX" with "=". If that doesn't do it, let us
know. Are the formulas otherwise working properly?

 

[isidey]

Another interesting idea! Unfortunately, it did not solve the problem.

In the interim, I have:

- tried out the file on another computer with another installation of
Office for Mac

- copied and pasted an entire worksheet

- duplicated a worksheet, copied that, and pasted back into the
original

all to no avail so far.

The actual calculations in the worksheets are working just fine. The
problem seems to be only in the "Trace Dependants" function, and only
for some cells. This is major for us, as the workbook is a complex
model, built over several months, and needs updating, modifying, and
ammending on a regular basis. In doing this, it is important to our
audit trail and to our presentation to eliminate any portions of the
worksheets that become redundant. There is also the possibility that at
some time in the future it may be necessary for an outside auditor to
audit the whole worksheet.

I have escallated efforts to solve this by contacting Microsoft paid
support this morning. I have separated out a single sheet of the
workbook into a separate workbook and ensured that there is no
confidential material in it. At MS's request, I have emailed this to
them for them to see if they can identify the problem and give me a
methodology to fix the entire workbook. You have been most helpful so
far - if you are curious enough about this conundrum, let me know, and
I could email you a file also.

Thanks again for your interest.

Ian

 

[isidey]

Bob,

Microsoft have now worked on this problem, and have identified that
there is a conflict between Tiger and Office for Mac which is causing
this problem. They have indicated that there will be a solution
included in a forthcoming update of either Office or of Tiger, and that
they are working with Apple to determine the solution.

The Trace Dependents button function works fine in Panther and on all
Windows versions of Excel with the worksheet in question, and indeed
works on some cells in Tiger.

For anyone who has a similar problem, and like me has no easy acces to
Windows or Panther machines, there are two workarounds that can get one
by until the patch is issued. Either access the Trace Dependants
through the Tools menu (which requires several keystrokes, and is too
cumbersome for someone who uses this function a lot), or set up a
Macro. I set up a Macro (using Record New Macro), teaching the Macro
through using Tools - Auditing - Trace Dependents. First time, it did
not work, just as the button did not work. So I opened the Macro, and,
not knowing what I was doing, edited it - I tried running the macro by
hitting the activation keys, which (a) worked, and (b) added a Return
in the Macro text. After that, for some bizarre reason, the Macro
worked fine, 99.9% of the time. Go figure.

Anyway, I thought I'd let you know, and cotribute this esoterica to the
general knowledge base.

Ian