|
|
|
|
|
|
|
|
|
|
| |
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" <nikki8327@discussions.microsoft.com> wrote in message
> news:236C25C5-FF54-4E40-BEC2-81EF3B128B61@microsoft.com...
> > 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
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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 |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <nikki8327@discussions.microsoft.com> wrote in message
news:B1C03393-1566-48BA-BB16-F3C7E9427EA0@microsoft.com...
> 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
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Notify me of replies