|
How do I set up a formula to insert text in excel?
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 |
| All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com