Excel 2007 : Convert text to formula and evaluate

[AJGoncalves]

Hello,

I need to access to data from an external excel file and get a value to the current worksheet:

cell A4 - path of the external excel file: C:\Projectos\QPLC\20100116\Files Sample\
cell B4 - file name of the external excel: I001 200912.xlsx
cell C4 - Text corresponding to the formula I need to use: =CONCATENATE(A4;"[";B4;"]";$B$2;"'!";"$B$1")
cell C5 - Evaluation of the cell C4: =Evalu(C4)

The cell C5 returns an error #VALUE! instead of the value of the cell B1 of the external excel file.

Can you give me any suggestion to solve this problem?

Thanks,
AJG

[mrice]

Try

Please Login or Register  to view this content.

[AJGoncalves]

It doesn´t work and return an error #REF!

If the path of the file is removed, it returns the correct value:

=INDIRECT(CONCATENATE("'[";B4;"]";$B$2;"'!";"$B$1"))

It is not possible include the full path to ensure that I am using the correct excel file?

Thanks,
AJG

[mrice]

Found this in Excel Help

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

[AJGoncalves]

Thanks!