How to change title in dynamic chart

**anthonyu** · 06-22-2012, 08:34 AM

Hi All,

Hope you can help me to find a solution. I have create a dynamic chart in excel. I have 3 data series. I put a filter so the chart only show the data selected by the filter.

This issue I have is that the title of the chart only refers to one cell. How can I make it refer to 2 cells.

I have attached my example.

thanks for your help.

graph.xlsx

**mshale** · 06-22-2012, 08:48 AM

Best way I can think of doing it is by adding a column somewhere with the formula:

=CONCATENATE(data!B23," - ",data!C23)

That will retun the value 1043183 - Apple, then you can direct the title of the chart to that cell

**Ace_XL** · 06-22-2012, 08:52 AM

As the concatenate (& other functions) are not valid on a cell chart, its best to use a helper cell to put the two together and reference your title to this helper cell

**mshale** · 06-22-2012, 09:41 AM

Originally Posted by mshale

Best way I can think of doing it is by adding a column somewhere with the formula:

=CONCATENATE(data!B23," - ",data!C23)

That will retun the value 1043183 - Apple, then you can direct the title of the chart to that cell

Originally Posted by Ace_XL

As the concatenate (& other functions) are not valid on a cell chart, its best to use a helper cell to put the two together and reference your title to this helper cell

That's why I said create a new column and put the formula in there, and then reference the new cell in the chart title

**anthonyu** · 06-22-2012, 10:04 AM

Thanks but when I then select Banana in the filter the formula is not working. It only works if I select the row 23.

Do you know if there is a forumal which will work when I change the filter selection?

**Andy Pope** · 06-22-2012, 10:11 AM

You have to use a formula that will dynamically pick information from the table. You can then link that cell to the chart title.

I added a helper column beside the table in order to pickup the first row in the filtered table.