Worksheet name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

borrie - 18 Aug 2006 14:15 GMT

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

Signature

borrie

Reply to this Message

Sloth - 18 Aug 2006 15:11 GMT

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

> Hello everyone,
>
[quoted text clipped - 7 lines]
>
> Borrie

Reply to this Message