I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step through
the formula.

If I take the complex formula and just type in the file name it works just
fine. So I do not understand why I can not just use the cell name because it
is essentially the same thing.

Try the INDIRECT function. If the range is named MyFile, then something like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet, so if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step through
the formula.

If I take the complex formula and just type in the file name it works just
fine. So I do not understand why I can not just use the cell name because it
is essentially the same thing.

I have experienced the same frustration, using Excel 2000 on Windows 2000,
and the indirect function constantly returns a #ref. My issue deals with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need to import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the workbook
name and the worksheet name are much longer that my example above. I am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in simpole
formula this function returns the filename/data range as expected. When
palced inside the Vlookup function it returns a #name error-- entered as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula =indirect(I7)-- this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns #REF

?? Am I missing something simple here or am I chasing an impossible solution
in getting the Vlookup function to accept anything other than typed text as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then something like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet, so if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step through
the formula.

If I take the complex formula and just type in the file name it works just
fine. So I do not understand why I can not just use the cell name because it
is essentially the same thing.

The workbook you are referring to needs to be open, is that the case?

--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
I have experienced the same frustration, using Excel 2000 on Windows 2000,
and the indirect function constantly returns a #ref. My issue deals with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need to
import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the workbook
name and the worksheet name are much longer that my example above. I am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in simpole
formula this function returns the filename/data range as expected. When
palced inside the Vlookup function it returns a #name error-- entered as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula =indirect(I7)--
this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns #REF

?? Am I missing something simple here or am I chasing an impossible
solution
in getting the Vlookup function to accept anything other than typed text
as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then something
like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet, so
if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step
through
the formula.

If I take the complex formula and just type in the file name it works
just
fine. So I do not understand why I can not just use the cell name
because it
is essentially the same thing.

NO the target workbook is not open. But it is not open when I type the formula
=vlookup(A3, [sample07Jan2007]sheet1!A43:E$14, 4) either. However the
manually entered formula does indeed perform the Vlookup function

"Peo Sjoblom" wrote:

The workbook you are referring to needs to be open, is that the case?

--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
I have experienced the same frustration, using Excel 2000 on Windows 2000,
and the indirect function constantly returns a #ref. My issue deals with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need to
import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the workbook
name and the worksheet name are much longer that my example above. I am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in simpole
formula this function returns the filename/data range as expected. When
palced inside the Vlookup function it returns a #name error-- entered as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula =indirect(I7)--
this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns #REF

?? Am I missing something simple here or am I chasing an impossible
solution
in getting the Vlookup function to accept anything other than typed text
as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then something
like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet, so
if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step
through
the formula.

If I take the complex formula and just type in the file name it works
just
fine. So I do not understand why I can not just use the cell name
because it
is essentially the same thing.

VLOOKUP can by itself handle closed workbooks but not your UDF nor INDIRECT


--
Regards,

Peo Sjoblom



"JR Hester" wrote in message
...
NO the target workbook is not open. But it is not open when I type the
formula
=vlookup(A3, [sample07Jan2007]sheet1!A43:E$14, 4) either. However the
manually entered formula does indeed perform the Vlookup function

"Peo Sjoblom" wrote:

The workbook you are referring to needs to be open, is that the case?

--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
I have experienced the same frustration, using Excel 2000 on Windows
2000,
and the indirect function constantly returns a #ref. My issue deals
with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need to
import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the
workbook
name and the worksheet name are much longer that my example above. I am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in simpole
formula this function returns the filename/data range as expected. When
palced inside the Vlookup function it returns a #name error-- entered
as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula =indirect(I7)--
this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns #REF

?? Am I missing something simple here or am I chasing an impossible
solution
in getting the Vlookup function to accept anything other than typed
text
as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then
something
like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet,
so
if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step
through
the formula.

If I take the complex formula and just type in the file name it
works
just
fine. So I do not understand why I can not just use the cell name
because it
is essentially the same thing.

Thanks for clarifying that subtle difference for me. I have invested a lot of
time trying to understand WHY it didn't work and WHAT I had done wrong with
the coding or formula.
BAck to the manual entry mode for each of our 200 to 600 spreadsheets.

Thanks again!
"Peo Sjoblom" wrote:

VLOOKUP can by itself handle closed workbooks but not your UDF nor INDIRECT


--
Regards,

Peo Sjoblom



"JR Hester" wrote in message
...
NO the target workbook is not open. But it is not open when I type the
formula
=vlookup(A3, [sample07Jan2007]sheet1!A43:E$14, 4) either. However the
manually entered formula does indeed perform the Vlookup function

"Peo Sjoblom" wrote:

The workbook you are referring to needs to be open, is that the case?

--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
I have experienced the same frustration, using Excel 2000 on Windows
2000,
and the indirect function constantly returns a #ref. My issue deals
with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need to
import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the
workbook
name and the worksheet name are much longer that my example above. I am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in simpole
formula this function returns the filename/data range as expected. When
palced inside the Vlookup function it returns a #name error-- entered
as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula =indirect(I7)--
this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns #REF

?? Am I missing something simple here or am I chasing an impossible
solution
in getting the Vlookup function to accept anything other than typed
text
as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then
something
like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source worksheet,
so
if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I step
through
the formula.

If I take the complex formula and just type in the file name it
works
just
fine. So I do not understand why I can not just use the cell name
because it
is essentially the same thing.

You can download and install Laurent Longre's add-in Morefunc from here

http://xcell05.free.fr/english/

it has a function called INDIRECT.EXT

http://xcell05.free.fr/english/moref...direct.ext.htm

that will work with closed workbbooks.

Harlan Grove wrote the PULL function which will do it as well

ftp://members.aol.com/hrlngrv/



--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
Thanks for clarifying that subtle difference for me. I have invested a lot
of
time trying to understand WHY it didn't work and WHAT I had done wrong
with
the coding or formula.
BAck to the manual entry mode for each of our 200 to 600 spreadsheets.

Thanks again!
"Peo Sjoblom" wrote:

VLOOKUP can by itself handle closed workbooks but not your UDF nor
INDIRECT


--
Regards,

Peo Sjoblom



"JR Hester" wrote in message
...
NO the target workbook is not open. But it is not open when I type the
formula
=vlookup(A3, [sample07Jan2007]sheet1!A43:E$14, 4) either. However the
manually entered formula does indeed perform the Vlookup function

"Peo Sjoblom" wrote:

The workbook you are referring to needs to be open, is that the case?

--
Regards,

Peo Sjoblom


"JR Hester" wrote in message
...
I have experienced the same frustration, using Excel 2000 on Windows
2000,
and the indirect function constantly returns a #ref. My issue deals
with a
Vlookup formula:
My users have a large number of workbooks and worksheets that need
to
import
beginning readings from a previous spreadsheet. The simple formula
Vlookup(A3, [filename]sheet1!A$3:e$14) works flawlessly. Both the
workbook
name and the worksheet name are much longer that my example above. I
am
attempting to automate the formula.
failed methods attemoted so far include:
1: created a VBA function(named LWT) that creates filename/data
range
combination "[sample07Jan2007]sheet1!A$3:E$14"; When placed in
simpole
formula this function returns the filename/data range as expected.
When
palced inside the Vlookup function it returns a #name error--
entered
as
Vlookup(A3, LWT(C2), 4)
2. typed the filename/data range into a cell I7,formula
=indirect(I7)--
this
returns #REF
3. Entered LWT(C2) into cell I1, formula =indirect(I1), returns
#REF

?? Am I missing something simple here or am I chasing an impossible
solution
in getting the Vlookup function to accept anything other than typed
text
as
one of the arguments?

"JMB" wrote:

Try the INDIRECT function. If the range is named MyFile, then
something
like:
=INDIRECT(MyFile&"WK1!h22")

Note that INDIRECT will always refer to H22 on the source
worksheet,
so
if
you move H22, you need to change the formula.


"ds_shades" wrote:

I am using a cell name to simplify my formulas

inside the cell that I named I put the path to a filename.
c:\users\documents\[filename.xls]


I have an Issue that when I create a simple formula like this
=[Filename]WK1!h22 it works just fine I get a result

But when I create a complex formula I get an #ref error when I
step
through
the formula.

If I take the complex formula and just type in the file name it
works
just
fine. So I do not understand why I can not just use the cell name
because it
is essentially the same thing.