asked on

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.

Bill Prew

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

SOLUTION

Neil Fleming

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Golden

ASKER

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.

ASKER CERTIFIED SOLUTION