-1

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!

zuluk
  • 1,557
  • 8
  • 29
  • 49
  • If `=ADDRESS(2;1;1;TRUE;"AK")` results in "AK1$A$2" shouldnt you remove "AK!" from the indirect? – Mr ML Dec 07 '18 at 09:11
  • This does not work, because `AK1$A$2` results in `A2`. And the `Indirect`-function should reference the `A2` in AK-sheet. – zuluk Dec 07 '18 at 09:14
  • As the Darren just answered you should just use the address. In the address you indicate which sheet ("AK") should be used in the reference. `=ADDRESS(2;1;1;TRUE;"AK")` results in cell A2 in sheet "AK". – Mr ML Dec 07 '18 at 09:17

2 Answers2

0

"AK!" & ADDRESS(2;1;1;"TRUE";"AK") would give AK!AK!$A$2 as the Address function already includes the sheet designation.

Use =INDIRECT(ADDRESS(2;1;1;"TRUE";"AK") instead.

Is "TRUE" correct? Shouldn't it be 0 (R1C1 style) or 1 (A1 style)?

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
0

Nesting Indirectthis way is the solution:

`=INDIRECT("AK!" & INDIRECT("AK!" & ADDRESS(2;1;1;"TRUE";"AK")))`
zuluk
  • 1,557
  • 8
  • 29
  • 49