OK - Here goes (I was hoping this would be a familiar problem beacause it
won't be easy to explain in detail.)
First of all, my guess is that I have somehow pushed Excel and/or my
computer beyond its performance limitations.
I have written a very complicated spreadsheet that calculates many long
formulas. There are over 100 named ranges/formulas in the worksheet, several
of which are array formulas. There are also complex Conditional Format and
Data Validation formulas within the sheet.
The problem regarding the #N/A being displayed, arose after I had already
finished a working version of the spreadsheet and I was attempting to upgrade
the performance. The worksheet had a column which originally displayed values
from another column after they had been filtered, using conditional array
formulas. The performance upgrade I was attempting was to further filter
those values by eliminating duplicate values and then displaying the unique
values in descending order.
Before, writing a formula to do this in my worksheet, I created a simple
version in a fresh workbook. It worked like a charm, so I wrote the same
basic formula into my worksheet, replacing the simple ranges with more
complex named array formula ranges. That's when the #N/A error appeared.
I thought I must have made some mistake in my formula so I began
troubleshooting the problem. The first thing I did was open the Function
Arguements window to try and see what part of the formula was generating the
error. But the Function Arguements window showed no errors and was displaying
the correct value that I was expecting. Then I tried using Name Manager to
evaluate the formula, and again, no errors and correct values given. Then I
tried using Tools/Formula Auditing/Evaluate Formula to see what I got. The
Formula Evaluator showed that the formula was evaluating correctly right up
until the very last step in the formula which was a conditional IF statement.
After evaluating this final step in the formula the Evaluator displayed #N/A.
I thought the conditions in the IF statement might be causing a circular
reference or some other problem so I replaced the conditions with some other
conditions which I knew couldn't possibly cause a problem, like
IF(ROW($C5)<100,Formula,FALSE). That didn't help.
I tryed turning off automatic calculation and going to manual. When I did
that, and then re-entered the formula in the cell, the cell displayed the
correct value, but when I hit F9 to recalculate, the #N/A error reappeared.
At that point It seemed like a problem that might be caused by a volatile
funcion that changes on every recalculation. Functions like NOW(), RAND(),
etc. Although I'm not using either of these functions in the worksheet I am
using the OFFSET function. I didn't try removing these functions becuse I
wanted to try other troubleshooting first.
The next step I took was to eliminate a named filtered array that was being
used in the formula by creating a worksheet column containing the values
instead. I thought that this named filtered array might be causing too many
levels of comlexity for Excel to handle. I then replaced the named array in
my formula with a direct refernce to the column range. That didn't help.
Finally, I tried copying the values in the new column I was referencing in
the formula and pasted the values, thereby eliminating the formulas. Voila -
the #N/A error was gone.
Unfortunately, this is not a practical solution to the problem in the long
term.
I couldn't figure it out. I've given up.
