Link part of file name to Excel cell

[Chan_Tze_Leong]

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 filename to the Cell so that when I name the file in the future Excel Apr Sample, the string in the Excel cell is MTD Apr CY2021.      

[#2]

@Chan_Tze_Leong 

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).

[#3]

Dear Hans, When I save the filename say to Apr, the output still shows Mar.

[#4]

@Chan_Tze_Leong 

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).

[#5]

This will basically reside is Sharepoint/MS Teams. Can it still run?

[#6]

@Hans Vogelaar Even though I saved as Apr, the link could not be updated.

[#7]

@Chan_Tze_Leong 

I cannot explain that, sorry.

And I don't know anything about SharePoint, so I cannot help you with that.

[#8]

@Chan_Tze_Leong 

Excel in Teams is actually Excel for web. It doesn't support VBA and GET.DOCUMENT function, as well as cell("filename").  Moreover, link on the current file, if check it in File->Info, starts from "https://...".

So far I don't know the way to receive the filename in Excel Online, if maybe with Office Scripts.

[#9]

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.