linking workbook cells

[jamiee]

Is there any way in a workbook function to call a specific worksheet
by looking at a corresponding value in a cell that contains it name?

What I mean is if I had an external workbook that I wanted to link to,
and that workbook had worksheets called 'alpha', 'beta'. 'omegea',
could I look at acell in my current workbook, say cell("a1") that has
the value "Omega" and by able to look at the worksheet called "omega"
in the external workbook. How would this be written as a formula?

Thanks

 

[Tim Otero]

Hi Jamiee,

Use INDIRECT. The below would take the worksheet name in A1 (in your case
'Omega') and lookup the value of the cell referenced in B1.
=INDIRECT("'"&A1&".xls'!"&B1)

As you can see, the formula follows the basic structure of a reference:
'Omega.xls'!B12

You could also use this within other formulas, such as VLOOKUP:
=VLOOKUP(C1,INDIRECT("'"&A1&".xls'!"&B1),4,FALSE)

where B1 contains the range or named range you want to search on Omega

tim