Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

I know that there Excel has a built in macro calle EVALUATE() which can treat string as formula so I created a User Defined Function evalu() to call the macro.

Please Login or Register  to view this content.

To demonstrate how it works, please see figure1.
I named two columns, "Price" and "t". At E3 and F3 I enter the formula I want as text.
Supposing an array formula like "{=evalu(E3)} would work like calculating Price*t on every row.

Figure 1

HTML Code: 

Indeed the evalu() function works, plx see figure2. However for formula with reference functions like INDEX, OFFSET, VLOOKUP (column F). The UDF evalu return only the first item (2) . It would have worked if I enter "=index(price, t)" for each row on Column F so I am sure the formula is just fine.

Figure2

HTML Code: 

It seems that when the formula string (F3) contains INDEX, the evaluate() macro/ UDF evalu doesn't know to return an array of result.

I once thought of modifying the UDF so that it first calculate the result somewhere else and then return back o the function but EXCEL UDF cannot alter any cells other than the cell that called it (caller).

Doesn't anyone know how to modified the UDf so that it would return the array of result?

It is essential to use the UDF here because I am building a generic pricing worksheets and there are like 100 columns of variables which formulae differs by products. The last solution I can think of is writng a macro then copy the formula string down everytime a formula is changed. However this is not cool

Please add CODE tags around your three line UDF.

Irrespective of whether or not this is the best approach to use one way of achieving your output for Column F might be:

INDEX(LOOKUP(t,ROW(t)-MIN(ROW(t))+1,price),0)

Actually, that's not correct... the sample is misleading given consecutive nature of t ... it can I think be simplified (and corrected!) to:

INDEX(LOOKUP(t,t,price),0)

(though above assume t is both unique and sorted in asc order)

Depending on nature of Eval call it might also be worth qualifying the Parent object by virtue of the range, eg:

Please Login or Register  to view this content.

(not really necessary in this specific context given use of named ranges with workbook scope)

Did you mean:

Please Login or Register  to view this content.

?

cryrus, as DonkeyOte already stated:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll add the code tags this time, but please use them from now on.

Originally Posted by romperstomper

Did you mean:

Please Login or Register  to view this content.

?

Thanks for the spot R....

Originally Posted by romperstomper

Did you mean:

Please Login or Register  to view this content.

?

Sorry I just registered today so I didn't know that I can use "#" for CODE, will certainly do so next time.

However adding the "Caller.Parent" doesn't resolve the problem.

Like I said this is just an example of what I am trying to do. The pricing worksheets will have over 100 named columns (and thus over 100 of these formulae, which differs by products). To create a generic template it is essential to make the UDF works.

In fact I think MS should really incorporate Evaluate() as a standard excel function as it is so powerful and would make excel modelling a lot easier generic enough to cater different products, assumption, models.

Cryrus

You can post the actual workbook - click Go Advanced, then use the Manage Attachments button.

Have you tried the formula suggestion in post #3 ?

INDEX(LOOKUP(t,t,price),0)

The point re: using Application.Caller.Parent was separate and more to do with ensuring that Evaluate is always processed against the appropriate object.

FYI, you don't need a UDF. Select D3, choose Insert-Name-Define, use Eval for the name and enter:
=EVALUATE(D$3)
in the Refersto box, then use:
=Eval
array entered in each column.
Note: this has the same INDEX issue as your UDF but that is down to the way Evaluate works.

Given the implicit precedents it would be "standard" practice to make whichever approach you use Volatile

UDF by virtue of:

Application.Volatile

XLM call (Name) by virtue of INDIRECT reference in the RefersTo: (with E3 active cell)

=EVALUATE(INDIRECT(ADDRESS(3,COLUMN(E3))))

However given the Volume of data at play you might be better of just enforcing a calculation as and when.

True.
I also suspect that in reality Data Tables might be useful too.

Who said anything about a hundred names? You use the same name in each column and it evaluates the formula in row 3 of that column.