I have a template that came from M$ that includes the following string:
=RIGHT(CELL("Filename",A1),LEN(CELL("Filename",A1))-FIND("]",CELL("Filename",A1)))
That string in Excel translates to a cell name given as the year and a sequential number. Generally I can parse even pretty arcane language, but this one I just don’t get. It definitely doesn’t work in Calc, but I’m more concerned with replicating the functionality than with the arcana of VBasic. Any ideas?
The formula takes trailing part of current file name (after “]”). See here and here.
As you didn’t give any information about your files naming scheme, as well as a sample expected output (aside of textual free-form description), it’s difficult to advise. Just note that Excel doesn’t mean sheet number for “Filename” func.
I had to get on a work computer with Excel installed to make it work. Here’s what it shows:
All of the tabs are in a single file named “My Name Invoice.xlsx” The tabs are named manually.:
Wish I knew the name of the site I stole it from!