Help with Cell names

[ds_shades]

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.

 

[JMB]

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.

 

[JR Hester]

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?

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.

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.

 

[Peo Sjoblom]

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

--
Regards,

Peo Sjoblom

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?

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.

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.

 

[JR Hester]

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

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

--
Regards,

Peo Sjoblom

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?

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.


:

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.

 

[Peo Sjoblom]

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


--
Regards,

Peo Sjoblom

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

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

--
Regards,

Peo Sjoblom

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?

:

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.


:

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.

 

[JR Hester]

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!

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


--
Regards,

Peo Sjoblom

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

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

--
Regards,

Peo Sjoblom


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?

:

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.


:

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.

 

[Peo Sjoblom]

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/morefunc/indirect.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

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!

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


--
Regards,

Peo Sjoblom

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

:

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

--
Regards,

Peo Sjoblom


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?

:

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.


:

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.