continual analysis toolpak problems

[PBcorn]

I have a WB with EDATE function, however, whenever the book is opened, I get
#NAME errors. Sometimes, unticking the analysis toolpak add-in on the add-in
dialog box, saving, closing the dialog box, closing the workbook, re-ticking
the analysis toolpak add-in then re-opening the workbook fixes the problem.
Now however, nothing seems to work. Analysis toolpak and the VBA analysis
toolpak are both shown as installed. This problem is getting extremely
annoying now. Any help appreciated.

 

[ShaneDevenshire]

Here are three solutions:

Method 1: Force Individual Formulas to Update
To force an individual formula to update, follow these steps: 1. Select a
cell that contains the error.
2. Press F2, and then press ENTER.
Repeat these steps for any other formulas that have not been updated.
Back to the top

Method 2: Force Formula Updating by Replacing Equal Signs
To force all formulas to update, follow these steps: 1. On the Edit menu,
click Replace.
2. Type = in the Find what box.
3. Type = in the Replace with box.
4. Click Replace All.

Back to the top

Method 3: Force Formula Updating by Using Keyboard Shortcut
You can also press CTRL+ALT+SHIFT+F9 to force all formulas to update.

 

[PBcorn]

thanks but this is just lifted from microsoft support. They aren't really
solutions as have to be repeated every time i open the workbook. Subsequently
i have found that the problem is more prevalent in cells with edate("external
range reference",- xmonths). I just added a row as an intermediary with the
external references in it and refered the edate function to this which seems
to help.

 

[Barb Reinhardt]

If it has to be done every time the workbook is opened, you could write a
short procedure to replace all of the equal signs on the worksheet every time
the workbook is opened. I know it's not an option I'd like, but it's an
option.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.