filename in a cell

[Danny Wilsher]

i can put the entire file name in a cell with =cell("filename") , but i would like to only have a portion of it in the cell. i save the job number i am working on in a folder and the complete path of the folder is displayed. i only want the job number to appear in the cell. thanks for any suggestions!!!!! danny.....

 

[Guest]

See the following:

http://www.mrexcel.com/board/viewtopic.php?topic=469&forum=2

 

[anno]

you'll need to save your workbook each time you paste this formula, otherwise it will return the sheetname you last pasted to for all worksheets

 

[NateO]

On 2002-02-27 16:58, anno wrote:
you'll need to save your workbook each time you paste this formula, otherwise it will return the sheetname you last pasted to for all worksheets

With the mid formula the workbook needs be saved. Not with:

Well, here's my offset. Bury this in your visual basic editor (you won't have to touch it after that):

Function SHEETOFFSET(offset)
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Name
End Function

Now, if your first sheet is going in row 1 then down, use the following formula:

=IF(ISERR(sheetoffset(ROW()-1)=TRUE),"",sheetoffset(ROW()-1))

If it's in row 2:

=IF(ISERR(sheetoffset(ROW()-2)=TRUE),"",sheetoffset(ROW()-2))

This variety is ready to go in any state of being....
This message was edited by NateO on 2002-02-27 17:02

 

[NateO]

Oh, Anno, I see what you're saying. Actually, cell("filename") will always display the active sheet (put the formla on the first sheet and reference it from the second and see what you get). By making it =cell("filename",a1), you "ground" the formula to show the sheet it's located in.

Which is why the following includes the a1 references:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))


Cheers,

Nate
This message was edited by NateO on 2002-02-27 17:09

 

[anno]

hi nate
you're right - my bad. i was using this one a while ago without the cell ref and then with the cell ref but i'd thought it played up in both instances. on checking my workbook again i see that it behaves fine with the cell ref included.
thanks

 

[NateO]

You're welcome Anno. And I haven't answered the original poster's question. They want the filename only.

Danny, try this:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Your file must be saved for the formula to work. Sorry about that.

Cheers, Nate
This message was edited by NateO on 2002-02-27 17:51

 

[NateO]

To get rid of the .xls on the end, try the following

=SUBSTITUTE(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),".xls","")

Hopefully this works (I'm at a terminal where I can't test it).

Cheers, Nate
This message was edited by NateO on 2002-02-28 08:22

 

[Danny Wilsher]

Thanks Mr. Nate Oliver,
I also am at home now and don't have office on this machine. i will email this code to myself and try in the morning. Thanks so much!!!

 

[NateO]

You're welcome Danny. I suspect it'll be functional, I'll be testing it tomorrow as well.

Cheers,

Nate