#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

 

[tburn]

Thanks,
Though, How do I add a normal module?
Also, the code I put at the top is the true part of an if statement. Will it still work if I include it in the statement?

 

[RoryA]

Yes it will.

To insert a module, press Alt+f11 to open the VB Editor. Then right click the workbook in the Project Explorer window (top left of the VB Editor) and choose Insert - Module. Then paste that code into the main blank window.

 

[tburn]

That works beautifully!
Thanks very much

 

[RoryA]

Glad to help. Welcome to the forum, by the way!

 

[tburn]

Ive just found an error with the above suggested code. If the EvaluateIt code is on the same sheet s the evaluated code, it works fine. Whene it's on a diffferent sheet, however, It's just returning a value of 1.
Is there any particular reason for this?

 

[RoryA]

The function evaluates the formula in the context of whatever sheet you have entered the function on. So if the formula should refer to a different sheet, it needs to specify that in the formula string. Does that explain what you are seeing? (BTW, like INDIRECT, it won't work with a closed workbook, if you are referring to other workbooks in the formulas)