How to resolve the #VALUE! error when using INDIRECT to generate an array of numbers?

Question

Hello,

I'm looking for some power users to help assist me with this issue, please.

I'm using the INDIRECT function in Excel to generate an array of numbers [i.e. {1;2;3}] and I want to check if the numbers in the array are greater than zero or not [i.e. IF({1;2;3}>0,TRUE,FALSE)].

That's the simplified version of what I am trying to accomplish, but to further simply the issue I am having see below.

For example, assume the below information:

(1) In cell B10: 30

(2) In cell B11: =INDIRECT("B10")*2, which equals 30*2, which equals 60, which is fine

(3) In cell B12: ={30}, which equals 30

(4) In cell B13: ={30}*2, which equals 60, which is fine

(5) In cell B14: =INDIRECT({"B10"})*2, which equals {30}*2, which gives the error #VALUE!

The INDIRECT function can find 30 in cell B10 in both (2) and (5) above, however once the curly brackets are included in the INDIRECT function (5) or the values result in an array from the INDIRECT function, the resulting value(s) cannot be combined with any operator to obtain a numeric or boolean result.

Any ideas as to why this happens (5), and how to fix it or get around it?

To give a few more details just for your reference, my array values come from like named ranges scoped to the different sheets. I'm trying to get TRUE or FALSE returned for each sheet that has a value greater than zero or not. Below is a real part of my formula. I guess I could create a VBA function to handle this, but I'm trying to avoid that.

Real part of my formula: INDIRECT("'"&SheetNames&"'!Safety_Stock")>0, where SheetNames is a named range of the array of sheets in the workbook and Safety_Stock is a named range of one cell reference. SheetNames is scoped to the workbook, while Safety_Stock is scoped to each sheet.

EDIT: I moved the curly brackets to the outside of B10; from "{B10}" to {"B10"}, but this doesn't change the question.

Answer 1

Thanks Vijay, but what more do you need?

I get that it's standard to want to know what's going on, why I'm doing this, what I'm trying to accomplish, etc. However, it's really just as simple as I have listed in the original post. 

=INDIRECT({"B10"})*2 equals ={30}*2, which gives the error = { **#VALUE!}**as the result. If this is simply not possible to resolve, then that is all.

Edit: the curly brackets should have been on the outside of "B10". But, the same issue still remains.

Answer 2

Hi Don

Greetings! I am Vijay, an Independent Advisor.

Within INDIRECT, only "B10" will be allowed. May be an alternate solution can be proposed once we understand complete requirement.