hi,
you will need a 2 column combo box.
set the combobox's column count property to 2.
your list fill range should look like this...
2 full compliance
1 partial compliance
0 non compliance
if the above was in range a1:b3 then your list fill range will be A1:B3
you may have to play with the column width property. set it to 10 to start.
in the combobox's code put this...
Private Sub ComboBox1_Change()
If ComboBox1.Value = 2 Then
Range("a5").Value = 2
Else
If ComboBox1.Value = 1 Then
Range("A5").Value = 1
Else
If ComboBox1.Value = 0 Then
Range("A5").Value = 0
End If
End If
End If
End Sub
adjust to fit your data.
Regards
FSt1
"CT1974" wrote:

I hope you can help.

I am currently designing a spreadsheet which will help us assess our
compliance with certain NHS standards.

Each standard has a cell. Each cell to the right of the standard has a pull
down list, where users can select 'Full Compliance', 'Partial Compliance' or
'Non-compliance' with regards to how well we are doing against each standard.

I would like each of these text responses to have a numerical value (e.g.
Full Compliance =2, Partial Compliance=1, Non-compliance=0) and I would like
this numerical value to automatically appear in the cell to the right of the
text response. Is this possible? I have tried the SUBSTITUTE function, but
this will only allow me to give one of the text responses a numberical value.
When I try and add details for the other two, errors occur.

Am quite new to using Excel, so any help would be much appreciated. Thanks