refer to data on multiple worksheets using hlookup/look up data on many worksheets?

[qiong]

Hi

I have data on many worksheets in the same workbook.

Is it possible to look up data on multiple worksheets using Hlookup,
which means can the second argument in the formula refer to multiple
sources?

If it is not possible, is there any other way I can do so?
I would like to select and display data according to a fixed order,
whereby the data is located in one of many worksheets in the same
workbook.
For eg, I have stock returns of many firms in many worksheets. Say I
would like to display in a single worksheet the returns of Firm D, Firm
Z, Firm R; whereby the data of these 3 firms are on separate
worksheets.

Thanks in advance
Keng

 

[Dave Peterson]

You can't use 3D references that way, but if you put the worksheet's name that
should be used in a cell (say column A of the same row), you could use something
like:

=VLOOKUP(B2,INDIRECT("'" & $A2 & "'!a:b"),2,FALSE)

 

[Dave Peterson]

Oops. And it'll work with =Hlookup(), too.

Hi

I have data on many worksheets in the same workbook.

Is it possible to look up data on multiple worksheets using Hlookup,
which means can the second argument in the formula refer to multiple
sources?

If it is not possible, is there any other way I can do so?
I would like to select and display data according to a fixed order,
whereby the data is located in one of many worksheets in the same
workbook.
For eg, I have stock returns of many firms in many worksheets. Say I
would like to display in a single worksheet the returns of Firm D, Firm
Z, Firm R; whereby the data of these 3 firms are on separate
worksheets.

Thanks in advance
Keng