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

NigelTufnel

Board Regular
Joined
Apr 3, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
The following formula seems to return the desired result:
Excel Formula:
=AND(N(INDIRECT({"A1","C1"})))
 
Upvote 0
Solution
The following formula seems to return the desired result:
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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