May 11 2021 01:55 AM
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.
May 11 2021 03:38 AM
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).
May 17 2021 01:56 AM
May 17 2021 02:25 AM
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).
May 17 2021 04:55 PM
May 17 2021 04:58 PM
@Hans Vogelaar Even though I saved as Apr, the link could not be updated.
May 18 2021 01:34 AM
I cannot explain that, sorry.
And I don't know anything about SharePoint, so I cannot help you with that.
May 18 2021 01:58 AM
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.
May 18 2021 10:13 AM