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

Nikki
You can use formulas for this type of thing if you want this done only
once. But if this is a repeating task I would suggest an event macro. If
you want to use an event macro you will have to decide when you want this
copying to take place. IOW, what event do you want to trigger the execution
of this event macro. When the file is printed? When it is saved? When it
is closed? When a specific cell content is changed? Post back and provide
more detail about what you have and what you want. HTH Otto
"nikki8327" wrote in message
...
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

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

Hi Otto,
Thanks for your advice.
I'd like the text to insert when a specific cell content is changed. Once
each respondent completes the 158 questions in the questionnaire on sheet 1,
I'd like them to be able to view their report on sheet 3, if this is
possible. I'm not very familiar with setting up macros unfortunately so any
advice will be much appreciated!
Thanks again,
Nikki

"Otto Moehrbach" wrote:

Nikki
You can use formulas for this type of thing if you want this done only
once. But if this is a repeating task I would suggest an event macro. If
you want to use an event macro you will have to decide when you want this
copying to take place. IOW, what event do you want to trigger the execution
of this event macro. When the file is printed? When it is saved? When it
is closed? When a specific cell content is changed? Post back and provide
more detail about what you have and what you want. HTH Otto
"nikki8327" wrote in message
...
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

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

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