CELL Formula Behavior

[dsipp]

I use this formula to but the file name in a cell. =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1).
This works perfectly. I then use another formula in a different cell to parse out a value from filename =MID(J1,FIND("e_0",J1)+3,2). This also works perfectly. Until I open a second excel workbook. the CELL formula up[dates in workbook A with the file name of workbook B and then the dominoes fall. I hit F9 and the name corrects but I am curious about the behavior.
Is this expected behavior? why? seems wrong. Ideas?

 

[RoryA]

You should always include a cell reference in the CELL formula unless you actually want the behaviour you are getting. Without one, it is evaluated in the context of the last altered/evaluated cell.

 

[dsipp]

Not clear on your response. What would I reference? Do you have an example of what you mean? Thank you for the response.

 

[RoryA]

Any cell in the workbook you are interested in - for example:

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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dsipp]

Any cell in the workbook you are interested in - for example:

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

I am not referencing any cell. The original formula is in J1. That is irrelevant as it could be anywhere. The second formula is where I ultimately want the result. I could wrap it all in one formula. As it stands today, the original formula is in J1. it returns the file name in J1 (as long as it is the only workbook open) then the second formula parses J1 to get the value I need in C1.

 

[dsipp]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Been a long time since I have been on the forum. thanks for the reminder. I have updated the profile. I am using 365.

 

[dsipp]

I am not referencing any cell. The original formula is in J1. That is irrelevant as it could be anywhere. The second formula is where I ultimately want the result. I could wrap it all in one formula. As it stands today, the original formula is in J1. it returns the file name in J1 (as long as it is the only workbook open) then the second formula parses J1 to get the value I need in C1.

I updated the formula to reference J1. this seems to solve the issue. Thanks for the kind response. Do you have and explanation of why this would behave this way? I mean what is a use case where one would want the name to update?

 

[Fluff]

If you have the new functions, you could change your formula to

=TAKE(TEXTSPLIT(CELL("filename",J1),"[","]"),1,-1)

 

[RoryA]

what is a use case where one would want the name to update

Any time you want information based on the last changed cell - remember there are a lot of other arguments to CELL than "filename".