Lookup where target worksheet may vary

[Nigel Barton]

I have a formula, similar to one below, that I want to use in many workbooks:

=HLOOKUP(TCI_TestCycle,[TestGroups.xls]REGR!Group,5,FALSE)

The worksheet REGR however will change. Sometimes it will have other values
depending on the workbook in which the formula is used. These worksheets are
in a separate workbook.

How, instead of the string REGR, can I insert a value from another cell in
the workbook?

Thanks,

 

[Sam Wilson]

If A1 contained "REGR" or another sheetname:

=HLOOKUP(TCI_TestCycle,INDIRECT("[TestGroups.xls]" & A1 & "!Group"),5,FALSE)

 

[Dave Peterson]

Sometimes you'll need to surround the workbook/worksheet name with apostrophes:

=HLOOKUP(TCI_TestCycle,indirect("'[TestGroups.xls]" & a1 & "'!Group"),5,FALSE)

And =indirect() will only work if the sending workbook is open.

Saved from a previous post.

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

I have a formula, similar to one below, that I want to use in many workbooks:

=HLOOKUP(TCI_TestCycle,[TestGroups.xls]REGR!Group,5,FALSE)

The worksheet REGR however will change. Sometimes it will have other values
depending on the workbook in which the formula is used. These worksheets are
in a separate workbook.

How, instead of the string REGR, can I insert a value from another cell in
the workbook?

Thanks,