Using a cell reference as a file name

[Philster]

We use work order numbers at my company. Each work order has a file by the
name WO[6 digit number]-I.xls. I want to create a new workbook that on the
left column has the list of the 6-digit numbers and the next column calls up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[StumpedAgain]

Try:

='C:\*path name*[' & A1 & '-I.xls]Sheet1'!$A$1

Is that what you're looking for?

 

[Dave Peterson]

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/

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

=====
Laurent's site seems to have been down for a few days.

A search of google came up with this:
http://www.download.com/Morefunc/3000-2077_4-10423159.html

I don't know if it's current, but it's pretty new. The setup.exe inside the zip
file is dated October 28, 2007.

We use work order numbers at my company. Each work order has a file by the
name WO[6 digit number]-I.xls. I want to create a new workbook that on the
left column has the list of the 6-digit numbers and the next column calls up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[Pete_UK]

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

 

[Philster]

Thank you all! That makes good sense, but both sites are not working.
Hopefully I can find this morefunc some where else. At least I know what to
do...

Thanks again!

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

We use work order numbers at my company. Each work order has a file by
the
name WO[6 digit number]-I.xls. I want to create a new workbook that on
the
left column has the list of the 6-digit numbers and the next column calls
up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[Philster]

Pete,

The cell reference build up works like a charm, but I'm having trouble
working the indirect function. Lets say that this "built up" path is in B1,
and I want it's value in C1. How do you use indirect? =indirect(B1) is not
working for me.

Thanks

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

We use work order numbers at my company. Each work order has a file by
the
name WO[6 digit number]-I.xls. I want to create a new workbook that on
the
left column has the list of the 6-digit numbers and the next column calls
up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[Dave Peterson]

The download.com site works for me.

Thank you all! That makes good sense, but both sites are not working.
Hopefully I can find this morefunc some where else. At least I know what to
do...

Thanks again!

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

We use work order numbers at my company. Each work order has a file by
the
name WO[6 digit number]-I.xls. I want to create a new workbook that on
the
left column has the list of the 6-digit numbers and the next column calls
up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[Dave Peterson]

Is the sending file open? =indirect() returns an error if its closed.

Pete,

The cell reference build up works like a charm, but I'm having trouble
working the indirect function. Lets say that this "built up" path is in B1,
and I want it's value in C1. How do you use indirect? =indirect(B1) is not
working for me.

Thanks

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

We use work order numbers at my company. Each work order has a file by
the
name WO[6 digit number]-I.xls. I want to create a new workbook that on
the
left column has the list of the 6-digit numbers and the next column calls
up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!

 

[Philster]

Oh my! No it was just a simple typo... Yes and I found the download.com file
as well.

Thanks for all your help!

Is the sending file open? =indirect() returns an error if its closed.

Pete,

The cell reference build up works like a charm, but I'm having trouble
working the indirect function. Lets say that this "built up" path is in B1,
and I want it's value in C1. How do you use indirect? =indirect(B1) is not
working for me.

Thanks

You can build up your cell reference like this:

="'C:\*path name*[WO" & A1 & "-I.xls]Sheet1'!A1"

where A1 contains the six digit number. However, you won't be able to bring
the data from that cell with this formula, as it only returns the string of
text that represents the cell reference. You would normally use this with
INDIRECT like this:

=INDIRECT(text_representing_cell_ref)

but unfortunately INDIRECT will only work with files that are open, and will
return a #REF error if the file is closed.

There is a free download, morefunc, which contains INDIRECT.EXT, which is
meant to overcome this problem and will work with closed files. However, I
read last week that the site was down, and it still is when I tried it just
now.

http://longre.free.fr/english/

Not sure if this helps.

Pete

We use work order numbers at my company. Each work order has a file by
the
name WO[6 digit number]-I.xls. I want to create a new workbook that on
the
left column has the list of the 6-digit numbers and the next column calls
up
the value of a specific cell in that work order's file. All the cells are
consistently in the same location.

I have been able to manually accomplish this with placing this in the new
workbook, sheet1, column B. Where i copy and paste the 6 numbers from the
cell in column A into this path in each cell in the B column.

='C:\*path name*[WO123456-I.xls]Sheet1'!$A$1

What I would like to see is some way to do this

='C:\*path name*[=A1]Sheet1'!$A$1

where the "A1" iterates in cells below.

I know my explaination is complicated but there must be some simple way to
do this that I am missing... Thank you in advance!