INDIRECT in an array formula

hsa.khamis

New Member
Joined
Jul 30, 2010
Messages
5
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
I didn't understand why you entered it in as an array formula?

If you put this in any cell (not an array formula)...
Code:
=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

New Member
Joined
Jul 30, 2010
Messages
5
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

New Member
Joined
Jul 30, 2010
Messages
5

ADVERTISEMENT

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

If you put this in any cell (not an array formula)...
Code:
=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! :LOL:

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. :confused:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
...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. :confused:
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

New Member
Joined
Jul 30, 2010
Messages
5

ADVERTISEMENT

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. :LOL:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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. :LOL:
Try...

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

for text-valued data.
 

hsa.khamis

New Member
Joined
Jul 30, 2010
Messages
5
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. :LOL:
Thanks All
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,212
Messages
5,509,865
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top