filename in a cell
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: filename in a cell

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Cheers,

    Nate

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com