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