INDIRECT with an array argument--Formula doesn't work, but F9 gives correct result

[NigelTufnel]

I'm trying to develop a complex formula that uses INDIRECT with an array argument. It does not work, although it does give the desired result if I debug using the F9 key. (But it doesn't give the desired answer when I use the Evaluate functionality from the Formula menu.)

Rather than present the complex formula, I've developed a much simpler example that has the same behavior. I'm wondering if someone can explain the reason it doesn't work.

This simplified example is designed to identify whether the contents of two cells, A1 and C1, both contain numbers.

=AND(ISNUMBER(INDIRECT({"A1","C1"})))

When I put numbers in cells A1 and C1--suppose they are 1 and 2 respectively--I get the answer FALSE, which is not what is desired/intended. So I'm violating some Excel formula rule. (Perhaps you can't use INDIRECT with arrays....but why not?)

If I highlight the INDIRECT({"A1","C1"}) section of the formula and hit F9, it replaces the section with an array of two numbers, i.e., {1,2}, which is what I intended. If I then highlight the complete formula (containing the {1,2} section), it gives me the result TRUE, which is what I want.

However, if I use the Evaluate formula capability from the Formula menu, it first tries to evaluate that same section of the formula and instead returns {#VALUE!, #VALUE!}, which explains why the result returns FALSE.

Question: Can anyone explain what is wrong with trying to use INDIRECT with an array?

Thanks.

 

[Tetra201]

The following formula seems to return the desired result:

=AND(N(INDIRECT({"A1","C1"})))

 

[NigelTufnel]

The following formula seems to return the desired result:

=AND(N(INDIRECT({"A1","C1"})))

Tetra201--

Thank you for your response and solution. Works like a dream. For whatever it's worth, substituting the T( ) function for the N( ) function allows it to work on text inputs.

I'm open to learning why the N( ) and/or T( ) functions are necessary, if anyone knows.

 

[Tetra201]

You are welcome.

Please take a look at this post -- it may answer some of your questions.