Problem when combining two working formulas into a single cell

I combined the following two formulas,

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-14)
and
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I combined the two formulas as follows

=MID(CELL("filename"),FIND("[",CELL("filename"))+5,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-14)&MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

into one cell. The first formula pulls part of the FileName into the cell. The second formula pulls the TabName into the cell. Each work appropriately in individual cells. But when you combine them, the second formula displays the TabName at opening until some action causes calculation, then the number is updated. This is not the case when the formula is in a cell by itself.

I am not sure, but I suspect it has something to do with the SEARCH command being used in the first formula versus the FIND command being used in the second formulas.
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
It works fine here on a file named "DTL-EE-1711-Rev3.xlsx", what file name are you using it with?  The offsets in the formulas assume that format of the file name, if you don't have that you can get errors.


»bp
0
Neil FlemingConsultant and developerCommented:
If I replicate your formula, but change it to show the whole filename, followed by a space, and the tabname, it seems to work fine. As follows:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-(FIND("[",CELL("filename"))+1)) & " " & MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

Open in new window


Is it some oddity relating to your "+5" and "-14"? If I use that on most filenames, it will show only the tab name because the file name is too short to fall with the +5 and -14 range.

Not sure if this helps?
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Initially the formula works fine. But does not work when changing from one tab to the other tab. The oddity in the +15 -4 is that we do not want the entire filename. The filename portion works fine because obviously it does not change. The entire problem is that each tab is named separately and as you switch from one tab to the other the cell retains the name of the tab that was active when the file was first opened. After you click two or three cells on the worksheet, the cell containing the tabname updates itself.
0
Bill PrewCommented:
Make sure you anchor the CELL() functions to a cell on the sheet, so instead of:

CELL("filename")

do:

CELL("filename", A1)


»bp
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Alas, I was using a slightly different iteration of the formula in what I was testing versus what I thought I was using.  Next time, I will check the formulas a little closer.  Thanks, learning!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.