|
Chart Title Using Autofilter Selection
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. |
Chart Title Using Autofilter Selection
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 |
Chart Title Using Autofilter Selection
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. |
Chart Title Using Autofilter Selection
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 |
Chart Title Using Autofilter Selection
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. |
Chart Title Using Autofilter Selection
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. |
| All times are GMT +1. The time now is 08:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com