#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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,386
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
INDIRECT expects a string representation of a range reference - it will not work with a formula string.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,386
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Aug 18, 2014
Messages
8
What would you need to know?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,386
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Firstly, is VBA an acceptable option?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,386
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Yes - and using either method you will get a macro warning since the EVALUATE command is an old XLM macro function.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,386
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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))
 

Forum statistics

Threads
1,082,555
Messages
5,366,293
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top