Scatter Graph questions

Hi all

I have a spreadsheet which ranks customers gold, silver and bronze based on their profit. I now want to populate this on a scatter graph but have some questions as this is the most complicated graph I have created:
1. Is there any way in which I can show the marker colours the same as my classification of the customer e.g. if I have ranked the customer as gold then I would like the marker colour to be gold also. I know you can do this manually but I have over 600 of these to do and that way is too time consuming
2. I want to be able to hover over the marker and for it to show the customer name, is this possible
3. How do I plot an average line

Please note I have a selection box where the end user selects what the graph needs to show e.g. show gold and silver customers only (this currently isn’t working).

I have attached an example...

In mine I have profit on the Y axis and Sales on the X axis but I need this switched

Any suggestions how I achieve this?

Regards

Paul

Excellent - I thought I would have had to rearrange the data in someway - thanks!!

I have two further questions.....
1. Does anyone know of a way (or a VBA code) which will display the customer name (column B) when you hover over the point on the actual graph?
2. How do I plot an average line?

I have updated the file...

I would want a line to go through the chart to show average sales 51.05 (average of D27:D46)

What is a class that traps the charts events?

I have found this link:

HTML Code: 

I can not get the code to work for me, any help?

Wow - i was getting into a real mess following that link i posted.

In order to get the data labels showing is this the only code needed?

Please Login or Register  to view this content.

did you have to do anything else to get it to work as i have to replicate this on another sheet e.g. are there any named ranges etc?

ps a HUGE thank you for helping me with this

ok excellent - I will rename my new range customers as i have alot more than the 20 that i posted in the example

Hi,

I have plotted the chart (a huge thank you) but it isnt as appealing as i hoped for as it is very clustered.

I have used a logarithmic scale on one of the axis however I can not do so on the other as it doesn’t plot negative values - just wondered if you (or anyone else) know any tricks that would display this any better?

Uploaded the latest file

I have tried that and I get a debug error

This is the code that I have:

Code on this workbook:

Please Login or Register  to view this content.

Code on Sheet1:

Please Login or Register  to view this content.

Code on sheet 2

Please Login or Register  to view this content.

Sorry, what would be the full code I would need in the Wookbook sheet? I'm a complete rookie with vba

Do the codes I have on sheet1 and sheet2 look ok as they differ slightly

This may be where I am going wrong, I will try later tonight when i am home

I have the customer sheet working but I have this highlighted in yellow as an error:

Please Login or Register  to view this content.

which means the lables are not appearing on the product sheet (sheet2) scatter graph


This is all the code I have:

This Workbook

Please Login or Register  to view this content.

Sheet 1:

Please Login or Register  to view this content.

Sheet 2

Please Login or Register  to view this content.

Where am I going wrong?

I changed the worksheet code to:

Please Login or Register  to view this content.

And it seems to be working up to a point but I still get the error:
"cht.GetChartElement x, y, element, a, b"

Which is on the Aintree Products sheet in VB

It says:
Run-time error 50290

Method 'get chart element' og object '_chart' failed

Then then following bit is highlighted in VB as yellow "cht.GetChartElement x, y, element, a, b"

Ok, i will upload the workbook - I just need to remove sensitive data.

I also get this error which seems to crash the sheet: Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale.Can I turn this off?

Please see the attached updated file

Thanks JosephP - really greatful for the help!!

Paul

JosephP - when i changed the zero and negative values it seemed to work. i think the graph was causing my spreadsheet to crash.