filename in a cell

Danny Wilsher

New Member
Joined
Feb 26, 2002
Messages
2
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.....
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0
You're welcome Danny. I suspect it'll be functional, I'll be testing it tomorrow as well.

Cheers,

Nate
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top