Work book title

[tintin1012000]

I want to put the title of the work book in cell A1
I have been using =CELL("filename",A1) but this returns
W:\APM\Prodqty\8882\Prod 2011\[8882wk4011.xls]shift reports

Is it possible just to return 40 ??

Thanks

 

[GTO]

As long as the end of the filename won't change length, maybe:

=MID(CELL("filename",A1),SEARCH(".",CELL("filename",A1))-4,2)

 

[tintin1012000]

Thanks GTO,

you might be able to help me with second part

I want to retun a cell value from (B7) in sheet1 from the following file on a network
Location is \\IEMAFS001\VOL1\APM\Prodqty\Electronic Shift Book\8882\Line 2\wk40.xls

But I want the new A1 that your code below has sorted to replace the 40 in the above network location eg if I have 50 in A1 then the code will give me
(B7) in sheet1 from file \\IEMAFS001\VOL1\APM\Prodqty\Electronic Shift Book\8882\Line 2\wk50.xls

 

[GTO]

I am afraid I am not following what you are saying

 

[tintin1012000]

OK let me try again

I have a file in a server location
\\IEMAFS001\VOL1\APM\Prodqty\Electronic Shift Book\8882\Line 2\wkxx.xls


I want to pull the content of one cell from within the work book location is sheet1 cell A33.

In my open excel sheet I now have 40 in A1 from your code and i want to use this 40 to change the link from \\IEMAFS001\VOL1\APM\Prodqty\Electronic Shift Book\8882\Line 2\wkxx.xls to \\IEMAFS001\VOL1\APM\Prodqty\Electronic Shift Book\8882\Line 2\wk40.xls