Color code data points on a scatter chart (2007) Using a pull down menu

Hello all,

First post, so I will do my best to explain.

I would like to colour code data points on a scatter plot I have created. But I don't know how.

I have the charts set up the way I want, but would like to add colour to the points via a drop down menu I have added to the table. The table and scatterplot is to be used by others so it can be updated with the results realtime.

Currently, I have the colours manually set up which requires manual change (which is not ideal). beside the series (the data used in the scatterplot) I have added an extra column with 3 "types" that I would like to have applied to the scatterplot which would change the colour of the points beased on the selection chosen.

I have included a version of what Im working on. The drop down items are on the "Information" sheet.

Thanks for any help