Trace Dependents doesn't work (precedents does!)

[tompclements]

I have 5 separate workbooks where the trace precedents works, but
trace dependents doesn't. All the happens is I get a dialogue box
saying that 'The Trace Dependents command found no formulas that refer
to the active cell' when clearly there are.

Obviously, there is something in common with all of them which is
preventing it from working. What concerns me is that the sheets are
somehow corrupt and are not calculating properly - which is going to
be a problem as the sheets are financial and need to be signed off by
an external auditor!

I have found threads suggesting other users have had this problem as
well, but have not come across any posts that have given any concrete
solutions or steers to a solution. Any ideas or areas I can look at in
order to try and rectifiy this?

Many thanks,

Tom.

 

[Niek Otten]

Hi Tom,

<What concerns me is that the sheets are somehow corrupt and are not calculating properly>

You can always rebuild the dependency tree with CTRL+ALT+SHIFT+F9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 5 separate workbooks where the trace precedents works, but
| trace dependents doesn't. All the happens is I get a dialogue box
| saying that 'The Trace Dependents command found no formulas that refer
| to the active cell' when clearly there are.
|
| Obviously, there is something in common with all of them which is
| preventing it from working. What concerns me is that the sheets are
| somehow corrupt and are not calculating properly - which is going to
| be a problem as the sheets are financial and need to be signed off by
| an external auditor!
|
| I have found threads suggesting other users have had this problem as
| well, but have not come across any posts that have given any concrete
| solutions or steers to a solution. Any ideas or areas I can look at in
| order to try and rectifiy this?
|
| Many thanks,
|
| Tom.

 

[Tom]

Hi Niek,

Many thanks for your reply.. I tried a CTRL+ALT+SHIFT+F9 and have also
tried Application.CalculationFullRebuild but neither seem to do the
trick. I have also noticed that Calculation is always showing in the
status bar. Iteration is turned off and there don't appear to be any
circular references.

Any more ideas?

Kind regards,

Tom.

 

[Tom]

Hi Niek,

Many thanks for your reply.. I tried a CTRL+ALT+SHIFT+F9 and have also
tried Application.CalculationFullRebuild but neither seem to do the
trick. I have also noticed that Calculation is always showing in the
status bar. Iteration is turned off and there don't appear to be any
circular references.

Any more ideas?

Kind regards,

Tom.








- Show quoted text -

Solved, just posting incase my experience can help someone else.

Through a bit of digging it turns out that excel can only handle a
maximum circa 65k references.. now typically my models have no where
near this amount of internal links and references, however I have
noticed that the addition of one sheet dramatically increased the file
size (far more than I would expect given what this sheet does).
Clearly this sheet is generating more links than are required, and
were expected. Removing this sheet, saving and reopening allows a full
recalc, and the dependents are back!

 

[Alan124]

Solved, just posting incase my experience can help someone else.

Through a bit of digging it turns out thatexcelcan only handle a
maximum circa 65k references.. now typically my models have no where
near this amount of internal links and references, however I have
noticed that the addition of one sheet dramatically increased the file
size (far more than I would expect given what this sheet does).
Clearly this sheet is generating more links than are required, and
were expected. Removing this sheet, saving and reopening allows a full
recalc, and the dependents are back!- Hide quoted text -

- Show quoted text -

Hi,

If the spreadsheet really corrupt, you can also try a tool called
Advanced Excel Repair. I have used it to repair many corrupt Excel xls
files on my damaged disks successfully. Its homepage is http://www.datanumen..com/aer/

Hope this helps.

Alan