I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.

Hi,

Use a cell to construct the required title text and the link the chart
title box to the cell.
http://www.andypope.info/tips/tip001.htm

Cheers
Andy

Roger wrote:
I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi,

Single click the chart title, then click the Formula Bar, type = and click
the cell in the spreadsheet where the filter criteria is displayed. Press
enter.

If you want the title to be a little bit more dynamic, for example you pick
a month from a filter and you want to display "Budget as of July"

The create a cell in the spreadsheet with a formula like
="Budget as of "&A1

Where A1 is the filter drop down result. Then use the original approach to
link the title to this cell with the formula.

If the cell A1 returned a date like 12/1/2008 then your formula would become
a little more complicated:

="Budget as of: "&TEXT(A1,"M/D/YYYY")

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Roger" wrote:

I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.

That was my first thought, I would just tie it to a cell, no problem.
However, when I click on the autofilter dropdown box and choose a
different name to filter the list the displayed name changes but the
cell reference stays the same. I need the title to display the same
name as the filter criteria. Is there a way to construct a UDF that
ties to the currently applied autofilter? If so then I could go that
route.

Andy Pope wrote:
Hi,

Use a cell to construct the required title text and the link the chart
title box to the cell.
http://www.andypope.info/tips/tip001.htm

Cheers
Andy

Roger wrote:
I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Nov 26, 3:10*pm, Shane Devenshire
wrote:
Hi,

Single click the chart title, then click the Formula Bar, type = and click
the cell in the spreadsheet where the filter criteria is displayed. *Press
enter.

If you want the title to be a little bit more dynamic, for example you pick
a month from a filter and you want to display "Budget as of July"

The create a cell in the spreadsheet with a formula like
="Budget as of "&A1

Where A1 is the filter drop down result. *Then use the original approach to
link the title to this cell with the formula.

If the cell A1 returned a date like 12/1/2008 then your formula would become
a little more complicated:

="Budget as of: "&TEXT(A1,"M/D/YYYY")

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Roger" wrote:
I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.

That's what I originally did, However cell A2 always contains the same
data. Autofilter just hides what is NOT selected, so if I tie the
title to cell A2 and cells A2 is not part of the filter then A2 gets
hidden but the title stays the same. It seemed easy but I have not
been able to figure out how to select the first visible cell after
applying the autofilter.

Hi,

I think you will need a VBA solution to this one.

Try this in a standard code module,

Public Function UDFMYCRITERIA(Data As Range) As Variant
'
' Return first visible cell contents
'
Dim rngCell As Range

On Error GoTo ErrUDF

For Each rngCell In Data.Cells
If rngCell.Height 0 Then
UDFMYCRITERIA = rngCell.Value
Exit Function
End If
Next
ErrUDF:
UDFMYCRITERIA = ""
Exit Function
End Function

Then the formula in the cell used to hold the chart title would be

=UDFMYCRITERIA(A2:A200)

where A2:A200 is the column containing information to be displayed.

Cheers
Andy

wrote:
That was my first thought, I would just tie it to a cell, no problem.
However, when I click on the autofilter dropdown box and choose a
different name to filter the list the displayed name changes but the
cell reference stays the same. I need the title to display the same
name as the filter criteria. Is there a way to construct a UDF that
ties to the currently applied autofilter? If so then I could go that
route.

Andy Pope wrote:

Hi,

Use a cell to construct the required title text and the link the chart
title box to the cell.
http://www.andypope.info/tips/tip001.htm

Cheers
Andy

Roger wrote:

I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Nov 27, 2:03*pm, Andy Pope wrote:
Hi,

I think you will need a VBA solution to this one.

Try this in a standard code module,

Public Function UDFMYCRITERIA(Data As Range) As Variant
'
' Return first visible cell contents
'
* * *Dim rngCell As Range

* * *On Error GoTo ErrUDF

* * *For Each rngCell In Data.Cells
* * * * *If rngCell.Height 0 Then
* * * * * * *UDFMYCRITERIA = rngCell.Value
* * * * * * *Exit Function
* * * * *End If
* * *Next
ErrUDF:
* * *UDFMYCRITERIA = ""
* * *Exit Function
End Function

Then the formula in the cell used to hold the chart title would be

=UDFMYCRITERIA(A2:A200)

where A2:A200 is the column containing information to be displayed.

Cheers
Andy



wrote:
That was my first thought, I would just tie it to a cell, no problem.
However, when I click on the autofilter dropdown box and choose a
different name to filter the list the displayed name changes but the
cell reference stays the same. I need the title to display the same
name as the filter criteria. Is there a way to construct a UDF that
ties to the currently applied autofilter? If so then I could go that
route.

Andy Pope wrote:

Hi,

Use a cell to construct the required title text and the link the chart
title box to the cell.
http://www.andypope.info/tips/tip001.htm

Cheers
Andy

Roger wrote:

I have a simple line chart based on the the data displyed from using
Autofilter. I select only one criteria at time to display. I want to link
the chart title to the filtered criteria. So when I choose a different
filter the chart title will change to the new filter. Is there an easy way
to accomplish this?

Thanks,

Roger

BTW, I have both XL 2003 & XL 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

--

Andy Pope, Microsoft MVP - Excelhttp://www.andypope.info

That works great Andy, thank you very much and thanks to all for the
suggestions.