Losing formulae that evaluation to #N/A

J

Jack Sheet

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,Rates!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
 
J

Jack Sheet

Jack Sheet said:
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,Rates!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
Click to expand...

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
 
B

Bob Phillips

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)
 
Top