Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
How to make an external link to a filename defined by a cell value
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
How to make an external link to a filename defined by a cell value
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 |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
How to make an external link to a filename defined by a cell v
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
How to make an external link to a filename defined by a cell v
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
How to link cell for value in another cell of pivot table? | Excel Discussion (Misc queries) | |||
How do I use a cell value as the filename in an external link? | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
GET.CELL | Excel Worksheet Functions |