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

Hi,

Have a look at the INDIRECT function.

Regards!
Jean-Guy

"Narasimha" wrote:

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

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.


"Narasimha" wrote:

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

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" wrote:

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.


"Narasimha" wrote:

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

Could you please explain with example because I am a basic user

"pinmaster" wrote:

Hi,

Have a look at the INDIRECT function.

Regards!
Jean-Guy

"Narasimha" wrote:

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

"'C:\TEMP\[Book1.xls]Sheet1'!$A$1" . I want to keep "Book1.xls" as a
reference so that I can get the value but I don't know that

"JLatham" wrote:

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.


"Narasimha" wrote:

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

Hi,

The INDIRECT function uses text strings as references, try this:

create a workbook...say Book1 and type a value in cell A1 in Sheet1, then
save and close, open a new workbook, in cell A1 type Book1, in cell A2 type
Sheet1, in cell A3 type A1, now in any cell type:

=INDIRECT("'["&A1&"]"&A2&"'!"&A3)

you should get the value of Sheet1!A1 of Book1.

Hope this helps!
Jean-Guy

"Narasimha" wrote:

Could you please explain with example because I am a basic user

"pinmaster" wrote:

Hi,

Have a look at the INDIRECT function.

Regards!
Jean-Guy

"Narasimha" wrote:

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

Hi

Sorry but I forgot that the indirect function does not work with closed
workbooks.

Regards!
Jean-Guy

"Narasimha" wrote:

Could you please explain with example because I am a basic user

"pinmaster" wrote:

Hi,

Have a look at the INDIRECT function.

Regards!
Jean-Guy

"Narasimha" wrote:

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

Thank you very much but it works if book1 file is opened simultaneously
otherwise the value is showing as #ref . could you please tell me how to get
rid of this too .
greatful to you if I get rid of this too .

"pinmaster" wrote:

Hi,

The INDIRECT function uses text strings as references, try this:

create a workbook...say Book1 and type a value in cell A1 in Sheet1, then
save and close, open a new workbook, in cell A1 type Book1, in cell A2 type
Sheet1, in cell A3 type A1, now in any cell type:

=INDIRECT("'["&A1&"]"&A2&"'!"&A3)

you should get the value of Sheet1!A1 of Book1.

Hope this helps!
Jean-Guy

"Narasimha" wrote:

Could you please explain with example because I am a basic user

"pinmaster" wrote:

Hi,

Have a look at the INDIRECT function.

Regards!
Jean-Guy

"Narasimha" wrote:

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

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 he
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" wrote:

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" wrote:

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.


"Narasimha" wrote:

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

Thanks alot and will ask you if I need any help

"JLatham" wrote:

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 he
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" wrote:

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" wrote:

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.


"Narasimha" wrote:

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