Losing formulae that evaluation to #N/A

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack Sheet - 16 Apr 2007 11:31 GMT

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

Signature

Return email address is not as DEEP as it appears

Reply to this Message

Jack Sheet - 16 Apr 2007 11:57 GMT

> Hi all
> I have a worksheet that contains a formula that takes the form
[quoted text clipped - 15 lines]
>
> Thanks

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

Reply to this Message

Bob Phillips - 16 Apr 2007 12:32 GMT

Jack,

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

Signature

HTH

Bob

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

>> Hi all
>> I have a worksheet that contains a formula that takes the form
[quoted text clipped - 28 lines]
> worksheet "Rates").
> Yuck - horrible

Reply to this Message