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 !!!

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


"Stevep4" wrote:

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 !!!

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" wrote:

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 !!!

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"



"Gary''s Student" wrote:

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


"Stevep4" wrote:

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 !!!

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

--
Gary''s Student - gsnu200826

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?



"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

--

Dave Peterson

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.




"Dave Peterson" wrote:

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

--

Dave Peterson

Try googling for morefunc. You'll find it.

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

Stevep4 wrote:

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.

"Dave Peterson" wrote:

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

--

Dave Peterson


--

Dave Peterson

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?



"Dave Peterson" wrote:

Try googling for morefunc. You'll find it.

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

Stevep4 wrote:

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.

"Dave Peterson" wrote:

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

--

Dave Peterson


--

Dave Peterson

Everyone will need a copy of that function/addin.

Stevep4 wrote:

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?

"Dave Peterson" wrote:

Try googling for morefunc. You'll find it.

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

Stevep4 wrote:

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.

"Dave Peterson" wrote:

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200826

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson