I have uploaded an Excel file that may offer a solution for you. It has one
Macro in it that I wrote to permit you to enter the path, filename and sheet
name for the other workbook (as Book1.xls or Book2.xls) and will create
formulas linking to cells in the other workbook in it. You may download the
file from here:
http://www.jlathamsite.com/uploads/for_narasimha.xls
just save the file to your hard drive and open it. It has explanation and
example screen shots.
Here is the code for the macro that does the work:
Sub CreateLinks()
'this will create links to
'cells in a closed workbook
'whose path, filename and sheet name
'are in cells on the sheet that is
'active when this code is run
'
'it will build references to the same
'cells the formulas are placed in and
'will place formulas in all cells that
'are selected when the code is run.
Const pathCell = "E1" ' change as needed
Const fileNameCell = "E2" ' change as needed
Const sheetNameCell = "E3" ' change as needed
Dim oneCell As Range
For Each oneCell In Selection
oneCell.Formula = "='" & Range(pathCell).Value & _
"[" & Range(fileNameCell).Value & "]" & _
Range(sheetNameCell).Value & "'!" & oneCell.Address
Next
End Sub
To put that code into your workbook, open it and then press [Alt]+[F11] to
get to the VB Editor. Choose Insert | Module and copy and paste the code
into the code module presented to you.
It may not be exactly what you want/need, but it could provide the basis for
something customized to your needs if we knew more about your workbook.
Things like what columns you now have the path/filenames in and what column
the final formula needs to go into and also information about the sheet
name(s) in the other workbooks.
Narasimha said:
thank you but not exactly .I want without opening files because it is not a
matter of 1 or 2 files and I have the list of file names only . when I paste
the list in column I want to get the values . that's why I used Concatenate .
Please help me on this regard .
JLatham said:
Easiest way to tell you how it should appear in a cell is to let Excel show
you one time:
Open two books: Book3.xls and one of the other books (Book1.xls for example).
In Book3, choose a cell and start by typing in an = symbol
Next, choose the other book (Book1) and choose a cell in a worksheet in it
Press the [Enter] key
Observe the formula created by Excel in the cell in Book3. It will contain
the full path to Book1, the name of Book1, the sheet and cell you selected in
Book1. This is how a reference to another workbook must be set up to be
functional.
:
Hi all ,
I am basic excel user.
I have some excel files, say Book1,Book2 etc in C:\Temp folder.
In another excel file say Book3 , When I enter Book1 or Book2 in cell A1 ,
the cell value of Book1 or Book2 respectively should come in cell which the
formula has.
I tried with concatenate but of no use (path of excel in Column A and file
name in Column B)
Could anyone please help me ?
Thanks in advance.
Narasimha
