Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: File name shown in spreadsheet?

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

    Default

    Hi It will be great if anyone could help me with this!

    Suppose the file name is 'AA 26 Mar 02'
    In Cell A1 (say) I have text 'BB 26 Mar 02'

    If I save the file with new name to 'AA 27 Mar 02' how can Cell A1(say) change to 'BB 27 Mar 02' such that whenever I change the date within the filename will automatically change the date in Cell A1.

    Thanks every one!!

    Looking for your quick reply

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

    Default

    Hi,

    You can try to insert the following formula into cell A1, but I am not too sure whether there are better ideas.

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

    HTH

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

    Default

    Thanks Tiger.. But I am not too sure what does D12 means in your suggestion

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

    Default

    oh tiger.. I try using that formula but it returns #Value!

    What has gone wrong?

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

    Chaju
    Try this one. If you are removing more than 2 characters at the start of the filename, you will need to change the 2 at the end of the formula. You could replace the hard coded "BB" with a cell ref to give you flexibility, which I think might be what BabyTiger is referring to. This builds on BabyTigers idea.

    "BB"&RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("[",CELL("filename"))-2)

    HTH
    Richard

    [ This Message was edited by: RichardS on 2002-03-26 19:51 ]

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

    Default

    thank.. but it has shown "BB 26 Mar 02.xls]Sheet1" instead of the intended BB 26 Mar 02


    Any more advise?
    Thanks

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Chaju, was testing in cell D12, that's why D12 showed up in the previous post. You can try the following, what I did was to replace D12 with Cell("filename"):

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

    HTH

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formula I have hyst posted would show BB 26 Mar 02.xls, if you don't want to see '.xls' as well, then try the following:

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

    HTH

    NP RichardS, but your formula is the correct one anyway, I have missed the "BB" part, silly me.

    Thansk

    [ This Message was edited by: BabyTiger on 2002-03-26 20:36 ]

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

    Sorry, TigerBaby closer than me. Try
    ="BB"&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
    Richard

    Sorry BabyTiger, messing around while you were posting

    [ This Message was edited by: RichardS on 2002-03-26 20:16 ]

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

    Default

    Thanks for both of you... but funny enough..

    Tiger, Your
    =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-5)

    will produce AA 27 Mar 02

    And RichardS, your
    ="BB"&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

    will produce BBAA 27 Mar02.xls

    Sorry for the advise needed again!!

    I look forward to hearing both of you soon

Some videos you may like

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
  •