The VLOOKUP(A8,InputSheet,8,FALSE) embedded in the below array formula
is supposed to retrieve a name range (dependent on criteria in that
particular row of data) and return data contained in that range. But,
the formula output is only recognized as a text string and not as a
name range.

{SUM((DateRangeBMTradeData=CP8)*(DateRangeBMTrade Data<=CQ8)*(VLOOKUP(A8,InputSheet,8,FALSE)))}

Currently the formula works only if I manually insert the name range's
name.

Is there a vba function that I can write that would convert the output
of the vlookup above (ex. Singapore_Benchmark) in such a way so that
the array formula would recognize its as the name of a range? If so,
how would I do this. If this is not possible, how do I get a range
address output from a name range so that the array formula will read it
as such and not an text string?

I am presently a novice regarding VBA. Would appreciate someone
pointing me in the right direction.
Thanks,
Rob

Maybe wrapping the final portion in =indirect() would help

.....*indirect(vlookup(....)))



cheloco wrote:

The VLOOKUP(A8,InputSheet,8,FALSE) embedded in the below array formula
is supposed to retrieve a name range (dependent on criteria in that
particular row of data) and return data contained in that range. But,
the formula output is only recognized as a text string and not as a
name range.

{SUM((DateRangeBMTradeData=CP8)*(DateRangeBMTrade Data<=CQ8)*(VLOOKUP(A8,InputSheet,8,FALSE)))}

Currently the formula works only if I manually insert the name range's
name.

Is there a vba function that I can write that would convert the output
of the vlookup above (ex. Singapore_Benchmark) in such a way so that
the array formula would recognize its as the name of a range? If so,
how would I do this. If this is not possible, how do I get a range
address output from a name range so that the array formula will read it
as such and not an text string?

I am presently a novice regarding VBA. Would appreciate someone
pointing me in the right direction.
Thanks,
Rob

--

Dave Peterson