trace dependent tool doesn't work but trace precendent doesn't

[marnie]

This is the 1st time this ever happened to me. My trace precendent tool is
working, but my trace dependent tool isn't. I know for a fact that the active
cell affects other cells. If I open up an older version of the file, both
tools are working. It's just this month's version. I even tried starting over
by opening last month's version, saving under a new name and adding the new
data. As soon as I add the new tab, this is a problem.

Does anyone know what could be causing this?

Here’s a history of my files. January I create a file with 1 tab for the
month. When February comes, I open this file, copy the January tab to a new
tab, rename it February and populate the tab with February data. Then save
the file under a new name. I now have 2 tabs, January & February. When March
comes around, I do it again. This is fine through March. It seems that when I
create the April tab, it becomes a problem. I even tried deleting the file
and starting over with the March file again. But as soon as I create the
April tab, the trace dependent function is gone. Also, “calculate†appears in
the status bar. I have it set for automatic calculation. Even if I hit F9, it
recalculates, but the status bar still says “calculateâ€. This didn’t bother
me, but now that I see my trace dependent isn’t working, I’m concerned. I
tried starting over a 3rd time and the same thing happened with the new file.
It’s not happening with any other files I copy and update. This file I’m
working with is fairly large, but I have many larger files that I update the
same way and haven’t had any problems.

Thanks
Marnie

 

[Niek Otten]

Hi Marnie,

Try CTRL+SHIFT+ALT+F9 (rebuild dependency tree). Works only in later versions of Excel (I don't remember from which one).
Alternative: Find and Replace = by = (equal sign replaces equals sign) for all sheets

Post again in this thread if that doesn't help.

BTW one of the reasons for the Calculate message can be that there are more than 65536 dependencies. Is there any chance that is
the case in your workbook?
It causes Excel to ignore the dependencies for recalculation, maybe also for the trace tool.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| This is the 1st time this ever happened to me. My trace precendent tool is
| working, but my trace dependent tool isn't. I know for a fact that the active
| cell affects other cells. If I open up an older version of the file, both
| tools are working. It's just this month's version. I even tried starting over
| by opening last month's version, saving under a new name and adding the new
| data. As soon as I add the new tab, this is a problem.
|
| Does anyone know what could be causing this?
|
| Here's a history of my files. January I create a file with 1 tab for the
| month. When February comes, I open this file, copy the January tab to a new
| tab, rename it February and populate the tab with February data. Then save
| the file under a new name. I now have 2 tabs, January & February. When March
| comes around, I do it again. This is fine through March. It seems that when I
| create the April tab, it becomes a problem. I even tried deleting the file
| and starting over with the March file again. But as soon as I create the
| April tab, the trace dependent function is gone. Also, "calculate" appears in
| the status bar. I have it set for automatic calculation. Even if I hit F9, it
| recalculates, but the status bar still says "calculate". This didn't bother
| me, but now that I see my trace dependent isn't working, I'm concerned. I
| tried starting over a 3rd time and the same thing happened with the new file.
| It's not happening with any other files I copy and update. This file I'm
| working with is fairly large, but I have many larger files that I update the
| same way and haven't had any problems.
|
| Thanks
| Marnie

 

[marnie]

Thanks Niek, and yes, I probably have more than 65,536 dependencies. Each
tab has 35 columns of lookup formulas. That would explain why this wasn't a
problem when I had 3 tabs, but as soon as make a 4th tab, it's a problem. I
have an older version of Excel, but I'll try each of the things you
mentioned. I do feel more comfortable that I don't have a virus. I may just
have to live without the ability to use the trace dependent.
Thanks again
Marnie