Forum Discussion
update Excel File Name (Rename an Excel Sheet), and it auto updates a specific cell in the workbook
I do have a question, that I seem to be hitting a wall on. Is it possible to update an Excel File Name (Rename an Excel Sheet), and it auto update a specific cell in the workbook? Here is the next part of the question. I need this program to be able to follow the file, regardless where it is saved. Meaning, if update the file on my computer, but email it to someone, I want the formula dynamic. I need it to follow the sheet NOT look for coding to “My documents”, or “My computer” to know what the cell name is. And if someone changes the file name on their computer, after I sent it to them, it auto updates the cell to the new file name they name it.
Budman36 It sounds like you want the filename in a cell. The function CELL() offers that sort of. CELL("filename") actually gives the full path/reference but with some additional work you can have it trim just the filename out:
=LET(path,CELL("filename"),start,SEARCH("[",path)+1,end,SEARCH("]",path),fn,MID(path,start,end-start),fn)if you don't have the newest version of Excel with the LET() function you can use:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)but maybe the full path would be even better for you?
mtarler This worked very well, on a local drive, personal computers, etc., but it doesn't work when uploaded to sharepoint? Any ideas?
CELL("filename") is not implemented for Excel Online
mtarler The MID works, but I am hitting a road block.
Here is my example of a book name:
21MJNXXX. Customer Name. Job Name. REV 1
I need to separate each section into a cell of there own...
21MJNXXX
Customer Name
Job Name
REV 1
The "Text to Columns" function will not work because it shows the formula. Not the actual text. I have tried =LEFT, =LEFT(RIGHT, .... =LEFT(MID... but I can't seem to get this right? Is it possible do the text to columns with code?
As variant
=LET(txt, A1, delim, ".", nDelim, LEN(txt)-LEN(SUBSTITUTE(txt, delim,"")), ch, UNICHAR(9999), n, SEQUENCE(1,nDelim+1), nn, SEQUENCE(1,nDelim+1,2), startPos,IFERROR(FIND(ch,SUBSTITUTE(txt,delim,ch,n-1))+1,1), numChars, IFERROR(INDEX(startPos,nn)-1,LEN(txt)+1)-startPos, TRIM(MID(txt,startPos,numChars)) )for
