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