Function to evaluate function as string

[Basil]

Hi all,

I have a dataset where for every row in one field (ie Column F) I need a
different function to be performed based on the entry in another column (B).

As such, I have been able to create a text string that represents the
correct function to be evaluated for each of the cells in this Column F. The
problem is I don't know how to convert this function as text to be a function
that is actually evaluated.

To give you an example, the cell F75 contains this formula
=IF(VLOOKUP(D175,Translation!$A$2:$D$40,2,FALSE)="",B175,TRIM(SUBSTITUTE(VLOOKUP(D175,Translation!$A$2:$D$40,2,FALSE),"B1",CELL("address",B175))))

The output is this text string:
=RIGHT($B$175,LEN($B$175)-SEARCH(" ",$B$175))

What I need is the evaluation of the above.

Is there a function that will generate this evaluated output?

I really need to avoid using code as once completed, this workbook will need
to be dynamic and sent to people in different companies, hence I need to
avoid the macro security issues.

Many thanks,

Basil

 

[Luke M]

You can do it with a named range. Select the cell where you want evaluated
formula to appear. Now, go to Insert - Name Define.
Give your function an appropriate name (e.g. "Eval")
For source, input:
=EVALUATE($F7)

You can now type into a cell:
=Eval
and it will evaluate the the text string located in column F of that row.

Note: Even though it's a legitimate function in a defined name, sadly, you
can not type the EVALUATE function directly into a cell.

 

[Bernie Deitrick]

Basil,

If you want to only use formulas, then you need a different approach, because what you are currently
doing requires VBA to complete.

How many different formulas does your VLOOKUP table contain? If it is only a few, then nest them
within a multi-level IF structure - if there are more than seven or if they are rather long so that
your formula becomes too long, then use additional columns of formulas and extract the value from
those cells.

HTH,
Bernie
MS Excel MVP

 

[Basil]

Thanks to both of you for your responses.

Luke, your answer was perfect - I have never come across that before in any
book or anywhere. Is there somewhere I can get a list of similar functions
that only work in defined names? It could be a whole new chapter of Excel for
me if other VB functions are also available.

Bernie, thanks also. This workbook is at the early stages - already the
lookup leads to almost 40 different functions. Ultimately it will be an
expanding list referenced by a dynamic named range (using offset). Hence
creating it in structured formulae/columns in the way suggested wouldn't have
been appropriate. Thanks though.

Basil