#Hello, I am trying to structure a worksheet that uses the HLOOKUP function
to show data selected from a worksheet in a different workbook. I have
several different workbooks with several different worksheets, all structured
the same. I would like to be able to define a named cell for the table array
value in the function and then by only changing the contents of the name
cell, redirect the HLOOKUP function to the appropriate worksheet to display
the desired data. But when I substitute the sheet in the formula with the
named cell containing the name of the worksheet in the other workbook, I get
#N/A. How do I assign the name of the sheet (or the named array in the
sheet) to a variable that I can use in the formula?

Use INDIRECT() within your formula

A1 = sheet name

=INDIRECT("'" & A1 & "'!A2")
the above formula will refer cell A2 of sheet mentioned in cell A1.

If this post helps click Yes
---------------
Jacob Skaria


"BKJ" wrote:

#Hello, I am trying to structure a worksheet that uses the HLOOKUP function
to show data selected from a worksheet in a different workbook. I have
several different workbooks with several different worksheets, all structured
the same. I would like to be able to define a named cell for the table array
value in the function and then by only changing the contents of the name
cell, redirect the HLOOKUP function to the appropriate worksheet to display
the desired data. But when I substitute the sheet in the formula with the
named cell containing the name of the worksheet in the other workbook, I get
#N/A. How do I assign the name of the sheet (or the named array in the
sheet) to a variable that I can use in the formula?

I have tried using Indirect, and if I enter the actual data references in the
formula, I receive the correct answer. And when I view the formula in the
function arguments dialog box all of the arguments are shown with the
Table_Array argument displayed as an array. But when I substitute the direct
references for named cells with the text of the references, the displayed
result is #REF and when I view the formula in the function arguments dialog
box, the INDIRECT() function is defined as 'volatile'.

"Jacob Skaria" wrote:

Use INDIRECT() within your formula

A1 = sheet name

=INDIRECT("'" & A1 & "'!A2")
the above formula will refer cell A2 of sheet mentioned in cell A1.

If this post helps click Yes
---------------
Jacob Skaria


"BKJ" wrote:

#Hello, I am trying to structure a worksheet that uses the HLOOKUP function
to show data selected from a worksheet in a different workbook. I have
several different workbooks with several different worksheets, all structured
the same. I would like to be able to define a named cell for the table array
value in the function and then by only changing the contents of the name
cell, redirect the HLOOKUP function to the appropriate worksheet to display
the desired data. But when I substitute the sheet in the formula with the
named cell containing the name of the worksheet in the other workbook, I get
#N/A. How do I assign the name of the sheet (or the named array in the
sheet) to a variable that I can use in the formula?

use the INDIRECT() Function to do this...

if B7 contains teh word Sheet2
in another cell
=INDIRECT(B7 & "!A1")
wil return teh value of range A1 in sheet2



"BKJ" wrote:

#Hello, I am trying to structure a worksheet that uses the HLOOKUP function
to show data selected from a worksheet in a different workbook. I have
several different workbooks with several different worksheets, all structured
the same. I would like to be able to define a named cell for the table array
value in the function and then by only changing the contents of the name
cell, redirect the HLOOKUP function to the appropriate worksheet to display
the desired data. But when I substitute the sheet in the formula with the
named cell containing the name of the worksheet in the other workbook, I get
#N/A. How do I assign the name of the sheet (or the named array in the
sheet) to a variable that I can use in the formula?