Auto format a chart data label

[Pauljj]

Morning all, I have a column chart that I have added data labels to

The chart can display 3 different types of data, depending on what the user chooses from the drop down menu

The data can be a percentage but if changed can be a whole number

So what I am trying to achieve is, when the user selects "Bookings" from the drop down option then SeriesCollection(2) formats the datalabel to "0%" otherwise the label should be formatted to "0,0"

The drop down is located in cell ref D11

My guess was to write some code in the sheet, create a Worksheet_Change(ByVal Target as Range)

Then say if D11 = "Bookings then

format the datalabel to % otherwise 0 but whilst this may be the right path my efforts for writing it have gone awry as I am getting a runtime error.

Any help would be much appreciated

Thanks
Paul

 

[GraH]

I have no clue how your data looks like, but something like this might work?

Book1
	B	C	D	E
2	Series	#	%	Labels
3	SA	65	30%	30%
4	SB	22	15%	15%
5	SC	92	71%	71%
6	SD	37	100%	100%
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=C3/SUM(C3:C6)
E3:E6	E3	=IF($G$1="%",TEXT(D3,"0%"),TEXT(C3,"0"))

Depending on your Excel version, you can link labels to a value from cells. Else you need to link them one by one, by selecting the label, then entering ="CellRef" in the formulabar.