Formula recalculates when it should not

[ozbeachbum]

Hi,
I have the following formula in a workbook which brings in the file name the workbook (19 REGISTER market data) except for the file extension.
=LEFT('[19 REGISTER market data.xlsx]COMPLETED ->'!$B$12,LEN('[19 REGISTER market data.xlsx]COMPLETED ->'!$B$12)-5)
If the workbook with the file name being referenced (19 REGISTER market data) is open and I make an entry in another workbook other then the referenced workbook (19 REGISTER market data) and press enter; tab or one of the arrows the destination workbook shows the file name for the workbook where I just made the entry, even though the formula has not changed.
Recalculating the referenced workbook (19 REGISTER market data), returns the correct result in the destination workbook.
If the referenced workbook is closed, the issue does not occur.
Any thoughts or solutions to correct this would be greatly appreciated.

 

[frabulator]

If I am reading the formula correctly, it only fires when the document is opened. It doesnt look like it checks if anything has changed, just that the book is there. If the book is opened, and a cell/sheet/book change trigger is fired, all formulas go through a recalculation. In other words, as long as the referenced book is opened, when a new cell is selected, the formula will recalculate. If the book is not opened, it has nothing to reference, so it will not calculate.

I will be the first to admit that I am not the best at formulas, but I am sure there is a way around this that would require some settings being changed. That being said, I would tackle the problem by writing a VBA Macro, and linking it to a button that can be pressed. It would not be an 'Automatic' result, like the one you have here (instead requiring a button press when ready), but it would only fire when you are ready for it to.

 

[FormR]

Hi, what formula do you have in this cell REGISTER market data.xlsx]COMPLETED ->'!$B$12 ?

 

[ozbeachbum]

Hi, what formula do you have in this cell REGISTER market data.xlsx]COMPLETED ->'!$B$12 ?

Here it is:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

 

[ozbeachbum]

If I am reading the formula correctly, it only fires when the document is opened. It doesnt look like it checks if anything has changed, just that the book is there. If the book is opened, and a cell/sheet/book change trigger is fired, all formulas go through a recalculation. In other words, as long as the referenced book is opened, when a new cell is selected, the formula will recalculate. If the book is not opened, it has nothing to reference, so it will not calculate.

I will be the first to admit that I am not the best at formulas, but I am sure there is a way around this that would require some settings being changed. That being said, I would tackle the problem by writing a VBA Macro, and linking it to a button that can be pressed. It would not be an 'Automatic' result, like the one you have here (instead requiring a button press when ready), but it would only fire when you are ready for it to.

Thank you so much for the reply.
I have enough trouble with formulas, I don't think I want to tackle VBA yet.
Cheers.

 

[Fluff]

You need to add a cell reference to the CELL function, like

=MID(CELL("filename",a1),SEARCH("[",CELL("filename",a1))+1, SEARCH("]",CELL("filename",a1))-SEARCH("[",CELL("filename",a1))-1)

 

[Fluff]

Another option as you have 2021 is

=LET(f,CELL("filename",A1),REPLACE(REPLACE(f,FIND("]",f),500,""),1,FIND("[",f),""))