Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup where target worksheet may vary
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, -- Nigel Barton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup where target worksheet may vary
If A1 contained "REGR" or another sheetname:
=HLOOKUP(TCI_TestCycle,INDIRECT("[TestGroups.xls]" & A1 & "!Group"),5,FALSE) "Nigel Barton" wrote: 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, -- Nigel Barton |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup where target worksheet may vary
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. Nigel Barton wrote: 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, -- Nigel Barton -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup source can vary -- how to code? | Excel Programming | |||
Editing the target worksheet | Excel Discussion (Misc queries) | |||
formula to lookup value and return value from cell at left of target | Excel Worksheet Functions | |||
Vary column width and row height in the same worksheet | Excel Discussion (Misc queries) | |||
Trapping Worksheet Target | Excel Programming |