How can I give text a numerical value in adjacent cell?

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

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:

 

[Pete_UK]

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

 

[Guest]

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

 

[Guest]

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

Claire