How to make an external link to a filename defined by a cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AndreCanelas - 16 May 2006 18:14 GMT

Hi,

I haven't been able to find a way to do this in the Help resources I found
so far:

I'd like to have a "report" workbook importing data from a number of other
"data" workbooks. I can make the external references manually, but that would
take a lot of time, and would have to be repeated every time there is a new
"data" file.
Is it possible to just write the filenames in certain cells and then make
the external references a function of the filenames (and paths) in those
cells? That way, every time there was a new "data" file it would be necessary
to add the proper filename to the list of paths.
Thanks in advance for your suggestions,

Andre Canelas
Delft, NL

Reply to this Message

Bill Manville - 17 May 2006 07:51 GMT

You can use the INDIRECT function to access cells in another workbook,
e.g.
=INDIRECT("[" & A1 & "]Sheet1!B2")
to access B2 on Sheet1 of the workbook whose name is in A1.

BUT the source workbook has to be open for INDIRECT to work, which
rather limits its usefulness.

If you have a set of cells that access data from, say, Book1.xls and
you want to create a similar set of cells that access data from
Book2.xls instead:
- copy the formulas and paste them where you want the new links.
- select the new formulas
- Edit / Replace / Book1.xls / with: Book2.xls / Replace All
[making sure that replace is looking at formulas and not looking at
entire cells only]

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

AndreCanelas - 17 May 2006 19:21 GMT

Dear Bill,

Thanks for the tip, but that's not going to work.
But what if I don't need to have the data in different files (workbooks) and
I can even have it just in different worksheets, in the same workbook. That
is possible and will prevent having to open the data files all the time.
Suppose also that all the data worksheets have the same layout, so that the
same type of data is in the same cell location. I still need a way to, in the
cell function, make the source worksheet name a function of another cell. In
the example below, I'd like to insert only the sheet name on the first row
and get the data in the following rows from certain coordinates of the
worksheet specified in the first row.

Sheet name CC64    CC70 <- introduce only this

Fermentation code SC_CC64    SC_CC70 <- get this from the
specified worksheet
Strain    CEN.PK 113-7d CBS288
Operator    Andre    Andre
Date start    23-Jun-05    28-Oct-05
Date end    29-Jun-05    1-Nov-05
... ... ...

Is there a way to do that?
Thanks in advance,

Andre

Reply to this Message

Bill Manville - 17 May 2006 23:38 GMT

If the source cells are in the same workbook then INDIRECT will work
(since by definition the workbook containing the cells is open).

=INDIRECT("'" & CellContainingSheetName & "'!$A$1")
for example

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message