INDIRECT in an array formula

[hsa.khamis]

Hi,

I created a new workbook and by default it has Sheet1 to Sheet3. I then assigned these values to different references: Sheet1!A1 = 10, Sheet2!B1 = 20, and Sheet3!C1 = 30

Back to Sheet1, I entered an array formula in the range A2 to C2 which is:
=ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C))

It evaluates as Sheet1!A1, Sheet2!B1, Sheet3!C1 in A2, B2 & C2 respectively.
When using INDIRECT and the reference is any of A2 to C2, the value is 10, 20 or 30 as expected.

However, when using INDIRECT function directly in the above array formula, it doesn't work and excel generates 3 #VALUE errors for any 3 cells picked up to have this array formula:
=INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)))

Although ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)) evaluates as Text, I tried to use TEXT function to make sure it will evaluate as Text. This did not help:
=INDIRECT(TEXT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)),0))

I tried "Evaluate Formula" tool and the result was that whatever inside the INDIRECT function evaluates as "Sheet1!A1" with quotes - because it is Text. But in the last step, when INDIRECT should give a value of 10 in this example, a #VALUE error appears!

Please assume that I have a good experience in Excel, array formulas (i.e. how to enter them and have the expected results) and I am an amateur Macros programmer who work around most my of the issues by using Excel Help or Googling.

 

[Aladin Akyurek]

Try...

A2, just enter and copy down:

=INDIRECT(INDEX(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)),ROWS($A$2:A2)))

 

[AlphaFrog]

I didn't understand why you entered it in as an array formula?

If you put this in any cell (not an array formula)...

=INDIRECT(ADDRESS(1,COLUMN(A:A),4,1,"Sheet"&COLUMN(A:A)))

...and then drag-copy it two more cells to the right, it references
Sheet1!A1, Sheet2!B1, and Sheet3!C1

 

[hsa.khamis]

Try...

A2, just enter and copy down:

=INDIRECT(INDEX(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)),ROWS($A$2:A2)))

Why someone sometimes becomes lazy to try things that his brain tells him to do before asking others?

I thought of using INDEX to make sure that it passes only one element of the array to INDIRECT but I didn't try it. NOW I tried it, it still gives #VALUE errors. Although I think this is not necessary.
=INDIRECT(INDEX(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)),COLUMN(A:C)))

Of course your ways guys are correct, but I was trying some array formulas and I ended using the one I created this topic for. It is entered in more than one cell, 3 in this particular example, just like a TRANSPOSE function. I was expecting it to work as what it was designed for. From the moment it failed to work, I insisted to understand why it did not work. I just want to understand why exactly this multi-cell array formula failed to work.

 

[hsa.khamis]

I didn't understand why you entered it in as an array formula?

If you put this in any cell (not an array formula)...

=INDIRECT(ADDRESS(1,COLUMN(A:A),4,1,"Sheet"&COLUMN(A:A)))

...and then drag-copy it two more cells to the right, it references
Sheet1!A1, Sheet2!B1, and Sheet3!C1

To be honest, I just came to know how powerful array formulas are 2 days ago. I used to use TRANSPOSE function and it is necessary to enter the formula as an array formula so the TRANSPOSE function works. Then I tried so many things and combinations by entering them as array formulas. Everything worked perfectly, except the situation I presented.

When I looked at your solution AlphaFrog, I felt I forgot what is a normal excel formula and that drag & drop (fill) way should always work!

Aladin Akyurek, as I wrote above, I wanted it to be a pure multi-cell array formula. Still can't understand why it doesn't work.

 

[Aladin Akyurek]

...Aladin Akyurek, as I wrote above, I wanted it to be a pure multi-cell array formula. Still can't understand why it doesn't work.

Enter in C2:

=N(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C))))

Select C2:E2.

Apply control+shift+enter.

Replace N(...) with SUBTOTAL(9,...) and try the same procedure.

 

[hsa.khamis]

Enter in C2:

=N(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C))))

Select C2:E2.

Apply control+shift+enter.

Replace N(...) with SUBTOTAL(9,...) and try the same procedure.

The little boy wanna play around and see how good people are in Excel.

Well, it happened that 10, 20 & 30 are numbers. If they were, let's say, text, then I will get zeros instead. At the end, INDIRECT should show the value of the ultimate cell it refers to.

So why is the formula works ok in two steps: 1) using it without INDIRECT + 2) then use INDIRECT to refer to the cells in step #1
and it doesn't work when making it in one step =INDIRECT(step1) ?


I hope my thread don't get deleted at the end of the day.

 

[Aladin Akyurek]

The little boy wanna play around and see how good people are in Excel.

Well, it happened that 10, 20 & 30 are numbers. If they were, let's say, text, then I will get zeros instead. At the end, INDIRECT should show the value of the ultimate cell it refers to.

So why is the formula works ok in two steps: 1) using it without INDIRECT + 2) then use INDIRECT to refer to the cells in step #1
and it doesn't work when making it in one step =INDIRECT(step1) ?


I hope my thread don't get deleted at the end of the day.

Try...

=T(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C))))

for text-valued data.

 

[hsa.khamis]

I've never used T() or N(). Your last thread helped me to modify the array formula to be:

=IF(T(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C))))="",N(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)))),T(INDIRECT(ADDRESS(1,COLUMN(A:C),4,1,"Sheet"&COLUMN(A:C)))))

Now IF will know whether the value is Text so it will use T() or a Number so it will use N().


It wasn't a useless topic. At least I learned new things.
Thanks All