Hello everyone,

I have a problem, i have a formula in one worksheet wich gets data from
another excel file: =+'C:test.xls]Worksheet1'!A8
The worksheets in the two files are exactly the same so my question is
can i replace Worksheet1 in the above formula so he takes the current
worksheetname of the current worksheet-file?

Kind regards,

Borrie


--
borrie
------------------------------------------------------------------------
borrie's Profile: http://www.excelforum.com/member.php...o&userid=37717
View this thread: http://www.excelforum.com/showthread...hreadid=573045

I believe your reference should look this

='C:\[test.xls]Sheet1'!A8

and to get "Sheet1" to be the current sheetname you can use this formula

=INDIRECT("'C:\[test.xls]"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1 ))-FIND("]",CELL("filename",A1)))&"'!A8")

I'll break it down for you

INDIRECT - is a function that calls a reference based on text
example: =INDIRECT("A2") will always reference cell A2 even if you delete,
move cell A2, or copy the cell the formula is in.

& - combines text together
example: ="A"&"2" results in "A2"

CELL("filename",A1) - outputs the full path of a cell. you will get
something like this
C:\Documents and Settings\YourName\Desktop\[Book1.xls]Sheet1

RIGHT, FIND, and LEN - are text functions used to pick out the sheet name

"borrie" wrote:


Hello everyone,

I have a problem, i have a formula in one worksheet wich gets data from
another excel file: =+'C:test.xls]Worksheet1'!A8
The worksheets in the two files are exactly the same so my question is
can i replace Worksheet1 in the above formula so he takes the current
worksheetname of the current worksheet-file?

Kind regards,

Borrie


--
borrie
------------------------------------------------------------------------
borrie's Profile: http://www.excelforum.com/member.php...o&userid=37717
View this thread: http://www.excelforum.com/showthread...hreadid=573045