We could revise it in Sheet2
(cols A and B, from row1 down)
as:

1 Text1
6 Text2
10 Text3

where response #s 1 - 5 will correspond to Text1,
6 - 9 to Text2, & 10 and above to Text3

Then in Sheet3, just amend the VLOOKUP to
find approx matches instead of exact matches, viz.:

Put instead in C2:

=IF(Sheet1!$C2="","",IF(ISNA(MATCH(Sheet1!$C2,Shee t2!A:A)),"Invalid
response# entered",VLOOKUP(Sheet1!$C2,Sheet2!A:B,2)))

And copy C2 down, as before

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"nikki8327" wrote in message
...
Thank you so much for your excellent advice - worked like a charm! The
only
problem I have now is that i have just realised that the text will need to
represent ranges of scores (i.e 1-5, 6-10). In 'Sheet 2' (as you have
described below) How do I insert these ranges into column A - from which
the
text will be produced?
Many thanks,
Nikki

"Max" wrote:

Try VLOOKUP, something along these lines

Assume this reference table (response# to text)
is set-up in Sheet2 cols A and B, from row1 down:
1 Text1
2 Text2
3 Text3
etc

Assume your questionaire is structured in Sheet1, where the response#'s
(1,2,3, ...) will be entered into col C, C2 down

In Sheet3 (assume this is your report sheet)
---------
Let's say you want the corresponding text for the responses made in
Sheet1
to appear in C2 down, viz.: Text1, Text2 to appear in C2 down

Put in C2:
=IF(Sheet1!$C2="","",VLOOKUP(Sheet1!$C2,Sheet2!A:B ,2,0))
Copy C2 down

Perhaps better with an error-trap for invalid responses (if any) made in
Sheet1

Put instead in C2:
=IF(Sheet1!$C2="","",IF(ISNA(MATCH(Sheet1!$C2,Shee t2!A:A,0)),"Invalid
response# entered",VLOOKUP(Sheet1!$C2,Sheet2!A:B,2,0)))

Any invalid response number entry in Sheet1
will then return: Invalid response# entered
(instead of #NAs)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"nikki8327" wrote:

Hi, I have a questionnaire that is administered on an excel sheet with
respondents typing their answers (i.e 1,2,3) into specific cells. Is
there
any way that I can create a formula (or something??) that inserts
response
relevant text into another sheet in the workbook, to create a report?
Many Thanks,
Nikki