conditional label position

[Greg Wheatley]

I would like to conditionally format the data labels position to be above the plot line in a scatter plot if a certain cell contains 'true' and below the plot line if that cell contains 'false'. Please advise with preferably a non-VBA solution. Thank you.

 

[NARAYANK991]

Hi ,

I doubt that a non-VBA solution is possible.

For a VBA solution , see the following link for code which can be modified to suit your requirement :

http://datapigtechnologies.com/blog/index.php/auto-adjust-chart-label-positions/

Narayan

 

[Hui]

Greg

Firstly, Welcome to the Chandoo.org Forums

To do what you propose I would add 2 Dummy Series
So you will have 1 series for the data, one where it is True and One series where it is False

The series will have the same range as the data series but will have the values #N/A! errors using a Formula

Then add a second and third series to the chart with the Dummy Series
Add the Labels to the Dummy Series above the Line & Below the line
Then set the line color of the Dummy data to None



see attached file

No VBA Required

 

[Hui]

I misread the question but now provide a quick update

Use the same technique just link the True/False Label columns to the yellow Data validation cell

\
or


See attached file:

 

[Hui]

Greg

I hope you get a chance to read:
https://chandoo.org/wp/2017/10/09/conditional-formatting-chart-data-labels/

 

[Greg Wheatley]

Wow, I am impressed with your quick reply and the quality of your response - thank you. I will let you know how I go.

 

[Greg Wheatley]

Hi mate. I am having some issues with your solution. Please have a look at my modification to your excel sheet. When you switch between true and false, the labels disappear. I have tried this solution http://www.excel-easy.com/examples/iferror.html but that disrupts the function of your solution. Any suggestions? I can't reformat the labels each time I use the sheet as I want to lock up the sheet for others to use. thanks. Greg.

 

[Hui]

see attached file

If there is still a problem, what version of Excel are you using ?

 

[Greg Wheatley]

When I select 'true' the labels disappear. I have Excel 2016.

 

[Hui]

So delete the Labels and remake them on your PC
Don't try and fix the existing labels
then save the file and try again