One more thing, how do I enable the lookup even though the 'sending file' is
closed?

"Kevin" wrote:

Thank you very much, Dave! It works very well now!

Kevin

"Dave Peterson" wrote:

If the the "sending" workbook is open, you can use =indirect().

But if you close that sending workbook and recalc, you'll see an error.

Kevin wrote:

The formula that I used in the Form.xls file is as follows:

=VLOOKUP(E2,[Price.xls]USD!$A$3:$X$121,HLOOKUP(F2,[Price.xls]USD!$A$1:$X$2,2,FALSE),FALSE)

In the Price.xls file are 3 worksheets of data to lookup from; USD, GBP,
Euro.

From the formula that I used above, it is the sheetname (USD, GBP, or Euro)
that I want to define in a cell K12 in Form.xls to select the currency price.
In other words, I'd like to substitute USD in the formula to cell K12 of my
Form.xls.

Is there a way to do that using just formulas?

Thank you
Kevin

"Dave Peterson" wrote:

The data to be retrieved is in a different workbook--not just a different
worksheet in the same workbook?

If that's the case...

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.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

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



Kevin wrote:

Hi there,

I have this situation:

I have 3 tables of data in 3 separate data worksheets (USD, GBP, Euro) in a
workbook data file (Prices). I want to lookup values in one of the 3 data
worksheets where I define the worksheet name (USD, GBP or Euro) in a cell of
my Form worksheet.

How do I define the path of the worksheet (USD, GBP, Euro) that I want to
lookup into in my lookup formula, by just defining the worksheet name in a
particular cell of my Form?

Thank you.

--

Dave Peterson


--

Dave Peterson