The second thing that comes to mind is that you are using relative
references ($F2) in the name definitions.
This is tricky in defined names because the question becomes "relative to
what"?
Do you really mean to do this?

What are the name definitions in R1C1 mode?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Shane Devenshire" wrote in
message ...

Hi,

The first thing that comes to mind is that you are using an Analysis
ToolPak
function which will return #NAME! errors if the ATP is not attached.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Harold Shea" wrote:

Hi, Charles:

As you surmised, there are three formulas, each with a different name.
Here
they are, with their names in front:

ED_BEGIN_DATE: =IF(OR('Master List'!$M1<"Y",'Master
List'!$F1=0),"",WORKDAY('Master List'!$F1,-50,'MGH Holiday
Calendar'!$B$2:$B$25))

ED_RELEASE_DATE: =IF(OR('Master List'!$M2<"Y",'Master
List'!$F2=0),"",WORKDAY('Master List'!$F2,-40,'MGH Holiday
Calendar'!$B$2:$B$25))

PROJECTED_FRD: IF(OR('Master List'!$M3<"Y",'Master
List'!$F3=0),"",WORKDAY('Master List'!$F3,-30,'MGH Holiday
Calendar'!$B$2:$B$25))

Both the formulas themselves and their named counterparts work fine in
the
sheet itself, and most of the time, when the macro inserts the name into
cell(s), it continues to work fine--most of the time. But then there are
the
apparently patternless exceptions I described. It's freaking me out. I'm
just
wondering why, when the macro insertion reults in NAME? appearing in the
cell, it goes away when I enter and exit editiing mode in the cell
without
actually changing anything.

"Charles Williams" wrote:

Hi Harold,

Whats in the Named Formulas?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Harold Shea" wrote in message
...

I have a formula to which I have assigned a name in Insert Name
Create--let's say "FormulaX". When I type "=FormulaX" into a cell on
the
worksheet, it works fine.

I use the name in some Worksheet_Change code as well, such that,
under
certain conditions, the formula is input into a cell. The relevant
part of
the code is as follows (I've omitted the stuff at the top and
bottom):

...

For Each myCell In myRng.Cells
With myCell
If (Me.Cells(.Row, "E").Value = "TENTATIVE" Or Me.Cells(.Row,
"E").Value =
"Tentative" Or Me.Cells(.Row, "E").Value = "tentative") And
(Me.Cells(.Row,
"M").Value = "Y" Or Me.Cells(.Row, "M").Value = "y") Then
Me.Cells(.Row,
"O").Formula = "=ED_BEGIN_DATE": Me.Cells(.Row, "P").Formula =
"=ED_RELEASE_DATE": Me.Cells(.Row, "Q").Formula = "=PROJECTED_FRD"
End With
Next myCell

...

(I haven't put any line breaks into the "If" statement yet, so its on
one
really long line in the module; the breaks you see here are just due
to
lazy
copy/paste.)

This works quite well most of the time--when the code deposits the
formula
name into a cell, it picks up data and returns the correct value.
SOMETIMES,
though, the cell it's entered into shows "NAME?" instead. I cannot
figure
out
the pattern of when it does and when it doesn't. When it does happen,
though,
I can double-click on the cell itself, and without changing anything
at
all,
exit it and the cdll now returns a value instead of an error message.
Also,
if I do a "Save As" under a new file name on a workbook that contains
these
bogus "NAME?" messages at the time, they clear up in the course of
saving
and
start returning actual values. (I've tried forcing a calculation on a
sheet
with the bogus name values, and that doesn't clear it up.)

Does anyone have any ideas on how to resolve this? I suppose I could
try
putting the formula itself right into the code, but then it won't be
as
easy
for users to edit, so I'd like to avpid that if possible. It's just
weird
that it works most of the time.