Worksheet tab in cell

D

Davey

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.
 
P

Paul B

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
 
D

Davey

Paul said:
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)
Click to expand...
That's what I have been using, without the 'FIND' part. It may make the
difference. I'll try it.

Thanks.

Davey.
 
D

Desert Piranha

Davey said:
Paul said:
Davey, not sure what you are using but this will return just th sheet name,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Click to expand...
That's what I have been using, without the 'FIND' part. It may mak
the
difference. I'll try it.

Thanks.

Davey.Hi Davey,
Click to expand...

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 cel
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 Su
 
Top