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

=MATCH(B1,{"Non-compliance","Partial Compliance","Full Compliance"},0)-1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"CT1974" wrote in message
...
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

One solution:

Say the source of the dropdown list is G1:G3. Enter in H1:H3 the numeric
values of compliance next to their text values respectively.

Say the cells for compliance text are in column B, then enter in cell C2
(first row being a header) this formula:

=VLOOKUP(B2,$G$1:$H$3,2,FALSE)

Drag it down as necessary!

Regards,
Stefi


€žCT1974€� ezt Ã*rta:

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

Assuming that your pull-down is in B2, put this formula in C2:

=IF(B2="","",IF(B2="Full Compliance",2,IF(B2="Partial Compliance",
1,0)))

You can then copy the formula down column C for as many standards as
you have.

Hope this helps.

Pete

On May 22, 8:39 am, 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

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

Wow, thank-you so much! Really very much appreciated!

Claire

"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