How do I set up a formula to insert text in excel?

[nikki8327]

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

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Max]

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,Sheet2!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 <at>yahoo<dot>com
----

"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

[nikki8327]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[nikki8327]

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,Sheet2!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 <at>yahoo<dot>com
> ----
>
> "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

[Max]

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,Sheet2!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 <at>yahoo<dot>com
----
"nikki8327" <[email protected]> wrote in message
news:[email protected]...
> 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,Sheet2!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 <at>yahoo<dot>com
> > ----
> >
> > "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