File name shown in spreadsheet? - Page 2
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: File name shown in spreadsheet?

  1. #11
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Try again
    ="BB"&MID(CELL("filename"),FIND("[",CELL("filename"))+3,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-3)

    or

    ="BB"&MID(CELL("filename"),FIND("[",CELL("filename"))+3,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-7)

    to exclude the .xls

    Richard

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's work very nice.. but however, Cell A1 will become other file name when I open other file and save the other file opened.

    How should that be cured?

    Thanks alot

  3. #13
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure what you mean. If the formula is in Cell A1, Cell A1 will contain the filename, exluding the .xls, and replacing the first two charcters with AA. The formula
    =CELL("filename") retunrs the full filename, including path a sheet name, you don't replace "filename" with youyr filename, you put it in exactly as it was posted.
    Richard

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-26 22:18, chaju wrote:
    That's work very nice.. but however, Cell A1 will become other file name when I open other file and save the other file opened.

    How should that be cured?

    Thanks alot
    You need to change

    CELL("filename")

    to

    CELL("filename",A1)

    Aladin


  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Richard,,

    I meant when File "AAA" is open with Cell A1 the formula you post up.. Cell A1 should give AAA.

    However, when i open other file with File Name "BBBB" and save this file (while file "AAA" is still opened) the Cell A1 in File "AAA" will become "BBBB" is that strange..??

  6. #16
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi chaju

    You could use a UDF for this

    See some very easy examples here

    http://www.ozgrid.com/VBA/WorkbookPath.htm



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