0

I have these two functions:

  1. =INDIRECT("A"& MATCH(A16,Sheet1!A:A,1))

  2. =INDIRECT(J3&"! PUT FUNCTION 1 HERE ")

Function 1 returns the value of cell A17, on Sheet 1.

Function 2 should return the value of A17 on Sheet 2 (the second indirect function refers to cell J3, which contains "Sheet2").

When I combine the two, I receive an error message.

Any help is greatly appreciated!

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Henry St
  • 33
  • 4

1 Answers1

0

As you wrote, function 1 returns the actual value of cell A16. If you nest func 1 in func 2, Excel would resolve this into:

=INDIRECT(J3 & "!" & Function1)
=INDIRECT(J3 & "!" & INDIRECT("A" & MATCH(A16, Sheet1!A:A, 1)))
=INDIRECT("Sheet2!My value in A16")

Obviously, this leads to an error. To solve this, suppress the second INDIRECT:

=INDIRECT(J3 & "!" & "A" & MATCH(A16, Sheet1!A:A, 1))
Physikbuddha
  • 1,652
  • 1
  • 15
  • 30