Dynamic Shart title = cell value

[John McCabe]

Have been using the dynamic title for years and it works GREAT.. Now
problem is the chart title comes from another tab, formula references the
other tab. Each chart has a tab with a dept. number and name.. Would like
to take dept. number from chart and rename tab. Do have macro to do this if
needed, but cannot get the dept. number into a cell on the graph page. And
still thank you again for many years ago helping with the dynamic chart
title - is great..

 

[ShaneDevenshire]

Hi John,

Sounds like you need a concatinated title: Suppose your title is in cell A1
and reads "My Title"

Then the following formula should replace the title in A1:

="My File "&MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

 

[John McCabe]

Thank You - Not sure why / how it works but thank you for help..

Hi John,

Sounds like you need a concatinated title: Suppose your title is in cell
A1
and reads "My Title"

Then the following formula should replace the title in A1:

="My File "&MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)


--
Cheers,
Shane Devenshire

Have been using the dynamic title for years and it works GREAT.. Now
problem is the chart title comes from another tab, formula references the
other tab. Each chart has a tab with a dept. number and name.. Would
like
to take dept. number from chart and rename tab. Do have macro to do this
if
needed, but cannot get the dept. number into a cell on the graph page.
And
still thank you again for many years ago helping with the dynamic chart
title - is great..

 

[ShaneDevenshire]

Hi John,

="My File "&MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

Here is how it works - this formula concatenates a label "My File " with the
results of the formula. The CELL("filename") function return something like:
E:\Excel For S&E\[Lesson9.xls]Sheet1. You want the sheet name, which is the
last item. Note that it follows the file name which is in []. The FIND("]"
function is looking for the numeric position of the ] in the result returned
by the CELL function. You want the file name which starts right after this.
The MID function returns 100 characters starting at the character 1 after the
] - the CELL("filename"))+1,100 portion of the formula. 100 characters
because we don't know how long your sheet names are. Note I should have put
31 since that is the longest sheet name allowed in Excel at present.

Hope this quick explanation helps.

--
Cheers,
Shane Devenshire

Thank You - Not sure why / how it works but thank you for help..

Hi John,

Sounds like you need a concatinated title: Suppose your title is in cell
A1
and reads "My Title"

Then the following formula should replace the title in A1:

="My File "&MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)


--
Cheers,
Shane Devenshire

Have been using the dynamic title for years and it works GREAT.. Now
problem is the chart title comes from another tab, formula references the
other tab. Each chart has a tab with a dept. number and name.. Would
like
to take dept. number from chart and rename tab. Do have macro to do this
if
needed, but cannot get the dept. number into a cell on the graph page.
And
still thank you again for many years ago helping with the dynamic chart
title - is great..