EVAL function not working

[RobbertH]

Hi all,

I have a problem with the the evaluation function which I can't figure out why.

I have the function which looks like:

=INDEX($A$19:$I$39,MATCH("Paid Admissions",$D$19:$D$39,0),COLUMN())

Now I have the same function as text which I am trying to evaluate with a user defined function "evaluation" (same as the morefunc add in) EVAL

INDEX($A$19:$I$39,MATCH("Paid Admissions",$D$19:$D$39,0),COLUMN())

The outcome of the EVAL treats the COLUMN() part as 1 instead of the column in which the eval function is placed.

When I evaluate just COLUMN() it works perfectly.....

Where do I go wrong??

Hope I am being clear enough...

 

[FastExcel]

Seems to work OK with my EVAL UDF function: see
http://www.decisionmodels.com/calcsecretsh.htm

 

[RobbertH]

FastExcel,

Thanks for your repley. Unfortunately for suggested function doesnt work for me. Like with my own public function it the eval treats the column() part as being 1. Therefore the index return the value from the right row (the match function works) but from column A instead of the column where the eval function is situated.

Crazy thing is that when I only evaluate column() it does return the column number of the column where the eval function is situated.

I did manage to tackle the problem, although not a very pretty solution.

i took the the ,column()) part out of the text that the eval function evaluates.
then I inserted the eval function like this:
=eval($A$44&","&COLUMN()&")")

I really have no idea why it doesnt work like it is supposed to. Funny thing is that when i add the same index
in text: INDEX($A$19:$I$39,MATCH("Paid Admissions",$D$19:$D$39,0),COLUMN())+INDEX($A$19:$I$39,MATCH("Paid Admissions",$D$19:$D$39,0),COLUMN())

is does work...

Bug in Excel??

 

[FastExcel]

OK: I finally managed to duplicate the problem. Looks like a bug to me. The simplest bypass is to convert the INDEX formula to an expression (add 0+ to the front if the answer is numeric).
I think its related to other Evaluate bug mentioned at
http://www.decisionmodels.com/calcsecretsh.htm
I shall have to add it to the Evaluate Quirks list at
http://fastexcel.wordpress.com/