#REF error in INDEX inside an INDIRECT function

[tburn]

Hi all,
I have the following function that multiplies the value in $F$5 with the value of a formula written in plain text on sheet 2 that is accessed via an index. It needs to be like this, because there needs to be a specific formula for each cell. and this formula needs go be multiplied with the value in $F$5.

The function is:

=INDIRECT($F$5&"*"&(INDEX(Sheet2!I$3:Sheet2!I$28,$D5)))

I believe my problem may be that I need to use an array formula, as the index may not work within the indirect, but i dont really know how to do that and was wondering if you guys can help me out?

Thanks loads,
Tommy

 

[RoryA]

INDIRECT expects a string representation of a range reference - it will not work with a formula string.

 

[tburn]

INDIRECT expects a string representation of a range reference - it will not work with a formula string.

Ah, ok. so is what I want to do possible?

 

[RoryA]

With code, yes. You could also probably do it using EVALUATE in a defined name but I'd need a much better idea of the layout of your worksheets.

 

[tburn]

What would you need to know?

 

[RoryA]

Firstly, is VBA an acceptable option?

 

[tburn]

Firstly, is VBA an acceptable option?

Hmm... Id rather not if at all possible...
Will it make it much simpler?

 

[RoryA]

Yes - and using either method you will get a macro warning since the EVALUATE command is an old XLM macro function.

 

[tburn]

Yes - and using either method you will get a macro warning since the EVALUATE command is an old XLM macro function.

Ok, then I suppose itll be fine.

 

[RoryA]

Add a new normal module to your workbook, then paste this code in:

Function EvaluateIt(sFormula As String)
    EvaluateIt = Application.Caller.Worksheet.Evaluate(sFormula)
End Function

Your formula will then become:
=$F$5*EvaluateIt(INDEX(Sheet2!I$3:Sheet2!I$28,$D5))