I have two Excel Sheets in the same workbook: Sheet 1 (Name is AK):
A B C
--------------------
1 | A2 | | |
--------------------
2 | 20 | | |
--------------------
Sheet 2 contains the following formula:
=INDIRECT("AK!" & ADDRESS(2;1;1;"TRUE";"AK"))
Now I want to add this formula into the sheet dynamically via VBA and wrote this line:
ActiveWorkbook.Worksheets("Sheet2").Range("q2").Formula = "=IF(A2<>"""",VLOOKUP(T2,INDIRECT(""AK!"" & ADDRESS(2,1,1,TRUE,""AK"")):INDIRECT(""AK!"" & ADDRESS(2,2,1,TRUE,""AK"")),2,0),"""")"
However I get the #BEZUG error (i am using Excel in German). What is wrong?
When I type the parts of the formula into a cell then I get the following results:
=ADDRESS(2;1;1;TRUE;"AK") results in AK!$A$1 as expected.
However =INDIRECT("AK!" & ADDRESS(2;1;1;"TRUE";"AK")) results in #BEZUG.
Thanks for any help!
Nesting Indirectthis way is the solution:
`=INDIRECT("AK!" & INDIRECT("AK!" & ADDRESS(2;1;1;"TRUE";"AK")))`