Forum Discussion
Link part of file name to Excel cell
I would like to link part of the filename to part of a cell. The name of the Excel file is Excel Mar Sample. The string in an Excel cell is "MTD Mar CY2021". I would like to like the Mar in the filen...
Create a defined name FileName that refers to =GET.DOCUMENT(88)
You can then use
="MTD "&MID(FileName,FIND(" ",FileName)+1,FIND(" ",FileName,FIND(" ",FileName)+1)-FIND(" ",FileName)-1)&" CY2021"
See the attached version (now a macro-enabled workbook).
Dear Hans, When I save the filename say to Apr, the output still shows Mar.
Unfortunately, that formula isn't updated automatically, you have to do that manually.
Here is another version. It uses a helper cell.
This time it is an ordinary workbook (not macro-enabled).
HansVogelaar Even though I saved as Apr, the link could not be updated.
- Excel recalculates prior to saving the workbook, not immediately after (if it did, and something changed, then it would prompt the user to save it again before closing because it changed since the last save operation and could create a confusing cycle for the user). But, it will update when next excel calculates (if you save the workbook, then enter a value in any cell you will see it update). Also, it should update when the file is closed/reopened.
- This will basically reside is Sharepoint/MS Teams. Can it still run?
