Forum Discussion

MMourits's avatar
MMourits
Copper Contributor
Oct 08, 2020

Filename

Hello    I´m using the Cell(filename) function, and this is working.  But I have 2 or more Excel sheets open, with the same formula but different file names.  But when I work in one file, it chan...
excel
Formulas and Functions
office 365
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Oct 08, 2020
Cell("Filename",CellRef) returns the current Workbook name,,, please share the file you are working with, will help us to examine.
MMourits's avatar
MMourits
Copper Contributor
Oct 09, 2020

Rajesh_Sinha 

I tried this but this does not work. 

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Oct 09, 2020
    Better upload the workbook and mark the cell in which you have applied the formula and let me examine,,, also if possible show me the expected output.
    • MMourits's avatar
      MMourits
      Copper Contributor
      Oct 09, 2020

       

      Rajesh_Sinha 

      Found it:) 

      by adding A1 in the referene it refferes to the current sheet and not the aktive one. 🙂 

       

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor
        Oct 09, 2020

        MMourits 

         

        This is the permanent solution. What you have to do is, use VBA macro for Workbook_Activate  event.

         

        Private Sub Workbook_Activate()

Range("E1").FormulaArray = "= MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1,SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-6)"

End Sub

         

        How it works:

        1. Open the 1st file, and to open the VB editor, press ALT + F11 .
        2. As soon you get the editor, find This Workbook Icon on left side in Project Explorer Windows.
        3. Click it.
        4. Copy & Paste above shown code.
        5. Press  ALT+Q to return to the Sheet.
        6. Now save the file as, MACRO ENABLED *.xlsm.
        7. Close the file.

        Repeat step 1 to 7 with other file.

         

        • Now if you open all files together, and transfer control from one file to other, Excel will apply the Workbook_Activate event ,, and apply the array formula in cell E1 ,, and in this way STOP using the old file name.

        Check the attached file for the code.

    • MMourits's avatar
      MMourits
      Copper Contributor
      Oct 09, 2020

      Rajesh_Sinha 

      There is no problem when I rename and safe this file. 

      But when I open 2 or more Excel files at the sam time, they all get the same name, instead of getting the name og the sheet they are used in. 

       

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor
        Oct 09, 2020

        MMourits 

         

        I got the issue ,, what you have to do ,,if you want to DUPLICATE it ,,, use SAVE as and assign another name,, rather then RENAME it. 

         

        And even you have RENAME it then also it works ,,,, what I have noticed when open the file cell has ### is problem with width,,, or you just select cell E1 press F2 and finish with Ctrl+Shift+Enter.

         

        • Check I've loaded files are 3,,, 1 is yours 2nd is after SAVED as 3 is RENAME ,, all are working.

         

        • In case if U open all 3 together and finds the issue ,,, just select cell E1 press F2 and finish with Ctrl+Shift+Enter.

        Let me find out a smart solution the I'll return soon to U.

        •  

Resources