Cell reference to a filename

[Stevep4]

I am putting together a data sheet for each of over 500 products. There is
great benefit to all if the data sheet uses the latest information from
source, rather that me (or someone) needing to keep on top of updating and
re-issuing specifications. So better to get it right first time!!

Each product has its own list of “vital data†(ingredients etc.) which will
be kept in a directory. Each of these files will be maintained by someone
else.

My data sheet is a template (Blank data Sheet) which will be almost totally
locked.
Each cell in the data sheet will call for the information in the "vital
data" file, plus it will also call for pictures etc. from a separate
reference directory which I am populating with information (some products
share data, such as packaging).

Each cell calling for data will have the following information, which is
quite straightforward. For example

In cell A3
='G:\Shared drive\vital data\[Book3.xls]Sheet1'!$C$4


Since there will be hundreds of "vital data" files, I want to be able to
enter the file name into a single cell in the blank sheet. (The file name of
the vital data will be the code number for the product)

So, what I want is something like…..

='G:\Shared\vital data\[“cell contents entered into Cell A5 in the blank
worksheetâ€.xls]Sheet1'!$C$4

So if I enter “book3†or “Book28†every cell (where necessary) will
automatically look for that specific file reference.

The reason for this is that the vital data sheets are being added to the
directory all the time.
The end user will only need to open the shortcut link to the “blank data
sheet†and type in the product code number in the single unlocked cell. This
file will immediately self populate itself with the vital data and can then
be saved and printed. If the Vital data is not available, the sheet will
remain blank.

And then I can go and do something else with my time !!!

 

[Gary''s Student]

Say we are using:

='C:\test\[Data.xls]Sheet1'!$B$3

but we want to get the Data.xls from a cell. In A1 enter:
Data.xls
and modify the equation above to:

=INDIRECT("'C:\test\[" & A1 & "]Sheet1'!$B$3")

However, for this to work Data.xls must be open!

 

[Joel]

from
='G:\Shared drive\vital data\[Book3.xls]Sheet1'!$C$4
to
=Indirect(A5 & "Sheet1'!$C$4")

where A5 = 'G:\Shared drive\vital data\[Book3.xls]

 

[Stevep4]

Yes,
that does work, but I cannot have the source data file open (there will be
over 500 source files !!)

What would I need to do so that in cell A1, I only need to put in the file
name - such as "Data" instead of "Data.xls"

Say we are using:

='C:\test\[Data.xls]Sheet1'!$B$3

but we want to get the Data.xls from a cell. In A1 enter:
Data.xls
and modify the equation above to:

=INDIRECT("'C:\test\[" & A1 & "]Sheet1'!$B$3")

However, for this to work Data.xls must be open!

--
Gary''s Student - gsnu200826

I am putting together a data sheet for each of over 500 products. There is
great benefit to all if the data sheet uses the latest information from
source, rather that me (or someone) needing to keep on top of updating and
re-issuing specifications. So better to get it right first time!!

Each product has its own list of “vital data†(ingredients etc.) which will
be kept in a directory. Each of these files will be maintained by someone
else.

My data sheet is a template (Blank data Sheet) which will be almost totally
locked.
Each cell in the data sheet will call for the information in the "vital
data" file, plus it will also call for pictures etc. from a separate
reference directory which I am populating with information (some products
share data, such as packaging).

Each cell calling for data will have the following information, which is
quite straightforward. For example

In cell A3
='G:\Shared drive\vital data\[Book3.xls]Sheet1'!$C$4


Since there will be hundreds of "vital data" files, I want to be able to
enter the file name into a single cell in the blank sheet. (The file name of
the vital data will be the code number for the product)

So, what I want is something like…..

='G:\Shared\vital data\[“cell contents entered into Cell A5 in the blank
worksheetâ€.xls]Sheet1'!$C$4

So if I enter “book3†or “Book28†every cell (where necessary) will
automatically look for that specific file reference.

The reason for this is that the vital data sheets are being added to the
directory all the time.
The end user will only need to open the shortcut link to the “blank data
sheet†and type in the product code number in the single unlocked cell. This
file will immediately self populate itself with the vital data and can then
be saved and printed. If the Vital data is not available, the sheet will
remain blank.

And then I can go and do something else with my time !!!

 

[Gary''s Student]

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

 

[Stevep4]

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

 

[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/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

 

[Stevep4]

unfortunately the download link does not work.

It seems very strange that I can change cell contents and the cell will take
the data from the new source without issue.

e.g
Change
='G:\Shared\vital data\[1000339.xls]Sheet1'!$B$16
to
='G:\Shared\vital data\[1007777.xls]Sheet1'!$B$16
and the cell will look to file 1007777 for its info.

But it wont let me enter the required file name into another cell so I could
have something like
='G:\Shared\vital data\[& A5 &.xls]Sheet1'!$B$16
There must be an easy solution.

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/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

 

[Dave Peterson]

Try googling for morefunc. You'll find it.

After Laurent Longre's addin is loaded, that is the easy solution.

unfortunately the download link does not work.

It seems very strange that I can change cell contents and the cell will take
the data from the new source without issue.

e.g
Change
='G:\Shared\vital data\[1000339.xls]Sheet1'!$B$16
to
='G:\Shared\vital data\[1007777.xls]Sheet1'!$B$16
and the cell will look to file 1007777 for its info.

But it wont let me enter the required file name into another cell so I could
have something like
='G:\Shared\vital data\[& A5 &.xls]Sheet1'!$B$16
There must be an easy solution.

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/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

:

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

 

[Stevep4]

Ok, managed to download the file and so far, it is working (the source file
is closed and a long way away.

Will the add-on still work if other people on other computers access the file?

Try googling for morefunc. You'll find it.

After Laurent Longre's addin is loaded, that is the easy solution.

unfortunately the download link does not work.

It seems very strange that I can change cell contents and the cell will take
the data from the new source without issue.

e.g
Change
='G:\Shared\vital data\[1000339.xls]Sheet1'!$B$16
to
='G:\Shared\vital data\[1007777.xls]Sheet1'!$B$16
and the cell will look to file 1007777 for its info.

But it wont let me enter the required file name into another cell so I could
have something like
='G:\Shared\vital data\[& A5 &.xls]Sheet1'!$B$16
There must be an easy solution.

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/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

Stevep4 wrote:

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

:

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

 

[Dave Peterson]

Everyone will need a copy of that function/addin.

Ok, managed to download the file and so far, it is working (the source file
is closed and a long way away.

Will the add-on still work if other people on other computers access the file?

Try googling for morefunc. You'll find it.

After Laurent Longre's addin is loaded, that is the easy solution.

unfortunately the download link does not work.

It seems very strange that I can change cell contents and the cell will take
the data from the new source without issue.

e.g
Change
='G:\Shared\vital data\[1000339.xls]Sheet1'!$B$16
to
='G:\Shared\vital data\[1007777.xls]Sheet1'!$B$16
and the cell will look to file 1007777 for its info.

But it wont let me enter the required file name into another cell so I could
have something like
='G:\Shared\vital data\[& A5 &.xls]Sheet1'!$B$16
There must be an easy solution.

:

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/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

Stevep4 wrote:

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

:

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")