Dynamic File Name Problem
 

The case is:

I have created a dynamic graph. It works fine. But today i recognize that
the file name which is excel using gets crashed. Let me explain.

I have 2 formulas with names one is "stock" and the other is "year". In the
graph when try to locate the formulas i add "=sheet1!stock" to the series and
"=sheet1!year" to the rows. Excel changes themm to dynamic file names as
"=file_name!stock" and "=file_name!year". No problem till here. It works very
fine. But when i close the file and open it again the names used in the graph
has been gone. It changes the formulas to "=[0]!stock" and "[0]!year" . So
bounded to the formula graphs won't work. I can't understand where is the
problem, can anyone help!

Dynamic File Name Problem
 

Not the most elegant way to do it, but if you but this formula in a cell, you
can use it to reference the sheet.

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

formula from cpearson.com
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tsunamic" wrote:

The case is:

I have created a dynamic graph. It works fine. But today i recognize that
the file name which is excel using gets crashed. Let me explain.

I have 2 formulas with names one is "stock" and the other is "year". In the
graph when try to locate the formulas i add "=sheet1!stock" to the series and
"=sheet1!year" to the rows. Excel changes themm to dynamic file names as
"=file_name!stock" and "=file_name!year". No problem till here. It works very
fine. But when i close the file and open it again the names used in the graph
has been gone. It changes the formulas to "=[0]!stock" and "[0]!year" . So
bounded to the formula graphs won't work. I can't understand where is the
problem, can anyone help!

Dynamic File Name Problem
 

Thx for the reply John but i found that the problem is a bug in 2007 and it
was solved by SP1.

"John Bundy":

Not the most elegant way to do it, but if you but this formula in a cell, you
can use it to reference the sheet.

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

formula from cpearson.com
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tsunamic" wrote:

The case is:

I have created a dynamic graph. It works fine. But today i recognize that
the file name which is excel using gets crashed. Let me explain.

I have 2 formulas with names one is "stock" and the other is "year". In the
graph when try to locate the formulas i add "=sheet1!stock" to the series and
"=sheet1!year" to the rows. Excel changes themm to dynamic file names as
"=file_name!stock" and "=file_name!year". No problem till here. It works very
fine. But when i close the file and open it again the names used in the graph
has been gone. It changes the formulas to "=[0]!stock" and "[0]!year" . So
bounded to the formula graphs won't work. I can't understand where is the
problem, can anyone help!

Dynamic File Name Problem
 

Great, thats good to know, i don't have 07 yet.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tsunamic" wrote:

Thx for the reply John but i found that the problem is a bug in 2007 and it
was solved by SP1.

"John Bundy":

Not the most elegant way to do it, but if you but this formula in a cell, you
can use it to reference the sheet.

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

formula from cpearson.com
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tsunamic" wrote:

The case is:

I have created a dynamic graph. It works fine. But today i recognize that
the file name which is excel using gets crashed. Let me explain.

I have 2 formulas with names one is "stock" and the other is "year". In the
graph when try to locate the formulas i add "=sheet1!stock" to the series and
"=sheet1!year" to the rows. Excel changes themm to dynamic file names as
"=file_name!stock" and "=file_name!year". No problem till here. It works very
fine. But when i close the file and open it again the names used in the graph
has been gone. It changes the formulas to "=[0]!stock" and "[0]!year" . So
bounded to the formula graphs won't work. I can't understand where is the
problem, can anyone help!