Evaluate function not working properly

[krishna334]

Hi All

Sheet1 of my excel sheet has the below string in cell B1:

[TABLE="width: 486"]
<tbody>[TR]
[TD="width: 486"]MATCH("PltStk",'C:\Users\kkumar\Desktop\ZPP AVL\[02JAN2018.xlsx]Sheet1'!$1:$1,0)

I defined a name "Result" as

= EVALUATE(Sheet1!$B1)

In cell C1 I want the match function to be evaluated, so I gave below formula:

=Result

But it is showing #REF ! error.

Could anyone tell me why this is happening

One more strange thing is that if i give a simple string like "1+1" in cell B1, "Result" works fine and C1 shows 2.
But this does not happen when I put that match which i described above[/TD]
[/TR]
</tbody>[/TABLE]

 

[bobsan42]

#REF generally means that something in you reference is not OK.

Normally I would check with a formula as simple as ='....'A1.
However - it turns out EVALUATE is not officially supported
check this post: https://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

A small quote from the full post:

[FONT=&quot]Mynda Treacy[/FONT][FONT=&quot] [/FONT][FONT=&quot]says[/FONT]<header class="comment-header" style="box-sizing: border-box; color: rgb(51, 51, 51); font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 18px;"><time class="comment-time" datetime="2017-04-14T08:31:11+00:00" itemprop="datePublished" style="box-sizing: border-box;">April 14, 2017 at 8:31 am</time>
</header>[FONT=&quot]Hi Kohlman,
I’m pretty sure the EVALUATE function doesn’t work with closed workbooks. I say ‘pretty sure’ because there is very little documentation on this function since it’s not officially supported.
[/FONT]
[FONT=&quot]Mynda[/FONT]

 

[krishna334]

Thank you Bobsan42.

I got the evaluate trick from the same link which you gave.
But did not go through the comment section.

Sad that it does not work with closed workbooks.

Is there any other method?