Thanks to this forum, I have learned to use the MID(..Cell(..))
functions to display the worksheet part of a filename in a cell, which I
use for automatically dating my weekly timecard, using the tab name as
the only reference I need to change. It then automatically dates the
days for me.

However, when I send a copy of the sheet to my dept. secretary, once on
her computer, the filename's path is completely different, and the
reference comes up as gibberish.

Any idea as to how to extract just the sheet name, without using the
whole filename? Or can I easily lock the cell's value, so that it
doesn't change even when the location changes?

Davey.

Davey, not sure what you are using but this will return just the sheet name,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Davey" wrote in message
...
Thanks to this forum, I have learned to use the MID(..Cell(..))
functions to display the worksheet part of a filename in a cell, which I
use for automatically dating my weekly timecard, using the tab name as
the only reference I need to change. It then automatically dates the
days for me.

However, when I send a copy of the sheet to my dept. secretary, once on
her computer, the filename's path is completely different, and the
reference comes up as gibberish.

Any idea as to how to extract just the sheet name, without using the
whole filename? Or can I easily lock the cell's value, so that it
doesn't change even when the location changes?

Davey.

Paul B wrote:
Davey, not sure what you are using but this will return just the sheet name,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That's what I have been using, without the 'FIND' part. It may make the
difference. I'll try it.

Thanks.

Davey.

Davey Wrote:
Paul B wrote:
Davey, not sure what you are using but this will return just the
sheet name,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That's what I have been using, without the 'FIND' part. It may make
the
difference. I'll try it.

Thanks.

Davey.Hi Davey,

I believe the workbook must be saved before the formula will work.

If your interested in a macro try this (change the "A3" to the cell
where you want the result).
It wil go into the "WorkBook Module"

This would be for all sheets in a Workbook, as they become active.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A3").FormulaR1C1 = ActiveSheet.Name
End Sub


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=525081