External linking to an entire worksheet

**JoelBU** · 10-03-2007, 10:52 AM

Hello everybody,

I have a number of Excel workbooks that use the same data in different ways. I would like to use a separate file for this data and have the other workbooks update automatically. The complicated part is that the number of data points changes and I would like to be able to see the data--properly formatted--in each of the workbooks.

Ideally I would expand this concept to multiple data files. The situation would look something like this:

INPUT
--------
File 1 - Data 1
File 2 - Data 2
File 3 - Data 3

"REPORT"
-------------------------------
File 5 - Data 2, Data 3
File 4 - Data 1, Data 2
File 6 - Data 1, Data 2, Data 3

I would like to enter my lab data into each of the "input" files and see it--as I said, complete and properly formatted--in each of the "report" files.

Any advice would be greatly appreciated. I am open to any strategy that allows me to enter data from lab experiments once and then see in multiple places.

Thanks!

--Joel--

**Mark@Work** · 10-03-2007, 01:34 PM

The best way into this is to load up 2 files then set up a link by selecting a cell in one of the files entering = then selecting a cell in the other file.
This will show you the way EXCEL constructs inter-file addresses.
Note that if you close the second file the address changes to be the entire file system address rather than just the file name.

The indirect function can be used to convert a text representation of an address into a reference to that address, but only for opened files.

Thus you can use the text manipulation functions (posibly in conjuction with the ADDRESS function) to allow you to reference an entirly different workbook by simply changing the file name in a single cell and constructing the rest of the addresses from that name.

For very large files you might find this becomes inefficent and slow.

Mark.

**JoelBU** · 10-03-2007, 09:44 PM

Hi Mark.

Thank you for your response. I tried your suggestion and found it a bit rough. I have two particular issues:

1) Linked cells do not inherit the proper format (height, width, highlighting, etc.) which makes some illegible. You mentioned that I can use text manipulation functions; being a newbie, I'm not sure exactly what you meant but I assume you were speaking to this issue.

2) Empty cells show up with zeros. Hiding zero values also hides cells that are displaying zeros intentionally.

Any thoughts on these particular issues or new approaches to the problem?

Thanks again. Cheers,

Joel

**shg** · 10-03-2007, 10:06 PM

Doing this will require VBA, and perhaps a fair amount depending on the amount of complexity omitted from your example.

I'd have thought that the data formatting would be performed on the Report sheets, rather than following the format on individual Input files -- that way it only needs to be done once and maintained (as in changed to new preferences) in one place.

Could you provide a more complete example of the input data, like the actual number of fields? And ditto for the report format.

And also, what is the logic (or type of rules to apply) in aggregating the inputs into the report? It's not obvious (to me) from your example.

**JoelBU** · 10-04-2007, 10:45 AM

Hello,

So here's the setup. I work in a biochem lab and we perform a series of experiments. Each time we perform an experiment, we collect SAMPLE, STANDARDS, and DYE REAGENT data. Sometimes we perform a calibration and collect CALIBRATION data. As I mentioned, I'd like to be able to enter each set of data only once.

To analyze an experiment, we need to look at the current SAMPLE and the historical STANDARDS and CALIBRATION data. To review the fitness of our equipment, we need to see the historical STANDARDS, DYE REAGENT, and CALIBRATION DATA.

My boss prefers to have each run report be a separate file. I'm starting to feel like the easiest solution is to simply have one workbook and make each new run a new, properly labeled worksheet, with worksheets for CALIBRATION, etc.

I've attached a ZIP file with two Workbooks: Fastin Dye Reagent... and Elastin Assay... Once opened, you'll notice that the Fastin Dye Reagent file contains only only one worksheet and that the Elastin Assay file contains an identical worksheet, in addition to a SAMPLE and STANDARDS worksheet.

I may be barking up the wrong tree by trying to make these separate files, but I'd like to push this a little farther. Thanks for helping out!

--Joel

**Mark@Work** · 10-04-2007, 12:29 PM

By text manipulation functions I simply meant such functions as LEFT(), RIGHT(), MID(), SEARCH(), etc.
i.e. functions that take a string of characters, manipulte them in some way and return a modified string of characters.
For example:
=LEFT("filename.xls",9)&"csv"
returns the value "filename.csv
In connection with your original query:
=INDIRECT("[Book2]Sheet1!A1")
gives the value of cell A1 of Sheet1 in the Excel file Book2
Each of the separate text elements in the above could be replaced by a cell reference where the cell contains the required value.
Thus if cell A1 is "Book2" and A2 is "Sheet1" then
=INDIRECT("["&A1&"]"&A2&"!"&"A1")
will give the required result
(watch out for A1 the cell reference and "A1" the text string)
Exactly the same effect can be given by:
=INDIRECT(ADDRESS(1,1,,,"[Book2]Sheet1"))
or with cells A1 and A2 set as above:
=INDIRECT(ADDRESS(1,1,,,"["&A1&"]"&A2))
As you say this can result in a rather awkward, clunky, spreadsheet, but it could be made to work if you realy want...
Mark.