Oct 08 2020 05:51 AM
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 changes the name in all other Cell(filename) functions, to the one I'm currently working in.
Is there a way to fix this?
Oct 08 2020 06:00 AM
Oct 09 2020 12:56 AM
Oct 09 2020 01:06 AM
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.
Oct 09 2020 01:31 AM
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.
Let me find out a smart solution the I'll return soon to U.
Oct 09 2020 01:45 AM
Hello
It is still doing the same, I opened all 3 files and safeed them.
But they stille show the same name, in the work sheet, but not the file name of the sheet the formula i used in.
Oct 09 2020 02:08 AM
Found it:)
by adding A1 in the referene it refferes to the current sheet and not the aktive one. :)
Oct 09 2020 02:40 AM
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:
Repeat step 1 to 7 with other file.
Check the attached file for the code.