Using INDIRECT Function and INDEX Function

[ChristopherG]

I am trying to use the INDEX function, within the INDIRECT function. Even
with a simple reference within the same workbook, I get the #REF! Error.

Examples:

=INDEX(JH!$A:$B,3,2) yields the contents of the cell in the range of the JH
worksheet, row 2, column 2.

If I imbed the above inside the INDIRECT function (needed so that the
reference to the JH worksheet may be a parameter, selected by the user from a
list) it get an error. In final production, the JH! would be replaced by a
text string "&$E$2&"! to allow for the selection in cell E2. I removed the
text string, and substituted a fixed cell reference to troubleshoot the error.

In cell E9 I have: ="INDEX(JH!$A:$B,3,2)"

In cell A9 I have: =INDIRECT(E9,1)

The result is: #REF!

I have been using both the INDEX and INDIRECT functions for a while, but not
with the INDEX imbedded inside the INDIRECT.

Thanks for any help or suggestions.

 

[Jacob Skaria]

Try the below with the Sheetname in E9

=INDEX(INDIRECT(E9 & "!A:B"),2,2)

If this post helps click Yes