Losing formulae that evaluation to #N/A
 

Hi all
I have a worksheet that contains a formula that takes the form
=IF(ISNA(My_Expression),0,My_Expression)
The precise formula is
=IF(ISNA(MATCH(A18,Rates!FromDate)),0,MATCH(A18,Ra tes!FromDate))
the overall expression therefore never evaluates to #N/A

This worksheet is intended as a template. The user duplicates the worksheed
are required via
Edit/Copy or move sheet/copy sheet.

However, whenever I duplicate the template as above, the corresponding cell
that should contain the above expression simply contains the expression
=#N/A

How do I get around this, please? I would rather not use VBA.

Thanks

--
Return email address is not as DEEP as it appears

Losing formulae that evaluation to #N/A
 

"Jack Sheet" wrote in message
...
Hi all
I have a worksheet that contains a formula that takes the form
=IF(ISNA(My_Expression),0,My_Expression)
The precise formula is
=IF(ISNA(MATCH(A18,Rates!FromDate)),0,MATCH(A18,Ra tes!FromDate))
the overall expression therefore never evaluates to #N/A

This worksheet is intended as a template. The user duplicates the
worksheed are required via
Edit/Copy or move sheet/copy sheet.

However, whenever I duplicate the template as above, the corresponding
cell that should contain the above expression simply contains the
expression
=#N/A

How do I get around this, please? I would rather not use VBA.

Thanks

--
Return email address is not as DEEP as it appears


Uff! I think I have solved my problem.

It does not seem to like my refering to a named range that is localised to a
different worksheet.
I partly got around the problem by globalising the named ranges.
But this created additional problems, because when I take a duplicate of the
worksheet it also creates a localised name range of the previous global
named range. So if I start changing the tables in the "Rates" worksheet
these changes will (I presume) not replicate in the duplicated worksheets
(which no longer refer to the global named ranges based on worksheet
"Rates").
Yuck - horrible

Losing formulae that evaluation to #N/A
 

Jack,

What you are doing (Originally) should work fine. I just knocked up a little
demo, and it works as expected, no #N/A.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sheet" wrote in message
...

"Jack Sheet" wrote in message
...
Hi all
I have a worksheet that contains a formula that takes the form
=IF(ISNA(My_Expression),0,My_Expression)
The precise formula is
=IF(ISNA(MATCH(A18,Rates!FromDate)),0,MATCH(A18,Ra tes!FromDate))
the overall expression therefore never evaluates to #N/A

This worksheet is intended as a template. The user duplicates the
worksheed are required via
Edit/Copy or move sheet/copy sheet.

However, whenever I duplicate the template as above, the corresponding
cell that should contain the above expression simply contains the
expression
=#N/A

How do I get around this, please? I would rather not use VBA.

Thanks

--
Return email address is not as DEEP as it appears


Uff! I think I have solved my problem.

It does not seem to like my refering to a named range that is localised to
a different worksheet.
I partly got around the problem by globalising the named ranges.
But this created additional problems, because when I take a duplicate of
the worksheet it also creates a localised name range of the previous
global named range. So if I start changing the tables in the "Rates"
worksheet these changes will (I presume) not replicate in the duplicated
worksheets (which no longer refer to the global named ranges based on
worksheet "Rates").
Yuck - horrible