#REF error in INDEX inside an INDIRECT function

tburn

New Member
Joined
Aug 18, 2014
Messages
8
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:

Code:
=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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
INDIRECT expects a string representation of a range reference - it will not work with a formula string.
 
Upvote 0
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.
 
Upvote 0
Firstly, is VBA an acceptable option?
 
Upvote 0
Yes - and using either method you will get a macro warning since the EVALUATE command is an old XLM macro function.
 
Upvote 0
Add a new normal module to your workbook, then paste this code in:
Code:
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))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top