Dynamic Chart Legend?

[slam]

I have a 'Chart' worksheet that pulls data from AF2:AY37 on a worksheet called 'Drivers & Standings'

Row 2 is a header row (specifically AG2:AY2) that is used along the X axis of the chart. The drivers are listed in AF3:AF37, and it is the drivers names that are shown in my chart legend, but the list is dynamic and can also include blanks from ""

I have my chart ignoring blanks in the data range AG3:AY37 thanks to VoG, but I also need it to ignore, and not include in the Legend, cells in the drivers column that are blank from "".

Does anyone know how to go about this?

Thanks

 

[Steve=True]

Hi Slam,

This can probably be done with VBA.

Don't know of an alternate solution without hiding the row/column of data.


Good luck.


Steve=True
www.exceldashboardtemplates.com

 

[slam]

Yeah, I found something at the link below, but I wouldn't know what to change to apply it to my own data:

http://www.ozgrid.com/forum/showthread.php?t=35755&page=1

 

[slam]

For anyone interested, I was able to do this by making the chart data auto hide rows that had an error (because there was no data to calculate). I used this code from jonmo1 to do that and I now essentially have a dynamic chart legend that only shows items on the legend for which there is data:

Private Sub Worksheet_Calculate()
Dim MyRange As Range, C As Range
Application.EnableEvents = False
Set MyRange = Range("A1:A36") 'Adjust range as needed.

For Each C In MyRange
C.EntireRow.Hidden = IsError(C)
Next C

Application.EnableEvents = True
End Sub