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.

The easiest way to do it is to open the other workbook, then create the
formula using mouse click into the other workbook. Once the formula is
complete, close it and Excel will automatically adjust it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Kevin" wrote in message
...
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.

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

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

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

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

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

One way is to use that =indirect.ext() function that comes with that addin by
Laurent Longre.



Kevin wrote:

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


--

Dave Peterson

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

Cheers,
Kevin

"Dave Peterson" wrote:

One way is to use that =indirect.ext() function that comes with that addin by
Laurent Longre.



Kevin wrote:

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


--

Dave Peterson