Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurance of text values across multiple worksheets
Sorry for any repetition with previous questions but I am completely hopeless
with excel functions and got lost when attempting to use functions contained within previous postings. My problem: I have a questionnaire for 27 sites. For each site I have the completed questionnaire entered into a seperate worksheet i.e 27 worksheets. The answer for each question is "Y", "N", "NK", or "NA". I need to provide a summary of the 27 sites which I want to enter on a blank master questionnaire on a seperate sheet. Can anyone help with a formula that counts the number of "Y", etc for each question (the cell for each question is the same for each site on the different sheets). If possible, can the summary cell indicate the number of Y's, the number of N's, the number of NK's, and the number of NA's?? It would also be fantastic to produce the results for each Y, N, NK, and NA as a %. Each questionnaire contains approx 200 questions so I would like to be able to paste the formula into each result cell on the summary sheet. Any assistance gratefully received! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurance of text values across multiple worksheets
Thanks Ardus,
I will give it a try in a bit. If it works like your example I will be totally grateful! May need additional help as I attempt it though as when I looked at the formulas you used I got lost! Thanks for your help. Jiq "Ardus Petus" wrote: I found a solution: linking the answer values to columns of summary sheet. Then you can perform calculations within summary sheet. Answer columns may be hidden in summary sheet. See example with 3 sites and 16 questions: http://cjoint.com/?ftmPAplI7b HTH -- AP "Jiq" a écrit dans le message de news: ... Sorry for any repetition with previous questions but I am completely hopeless with excel functions and got lost when attempting to use functions contained within previous postings. My problem: I have a questionnaire for 27 sites. For each site I have the completed questionnaire entered into a seperate worksheet i.e 27 worksheets. The answer for each question is "Y", "N", "NK", or "NA". I need to provide a summary of the 27 sites which I want to enter on a blank master questionnaire on a seperate sheet. Can anyone help with a formula that counts the number of "Y", etc for each question (the cell for each question is the same for each site on the different sheets). If possible, can the summary cell indicate the number of Y's, the number of N's, the number of NK's, and the number of NA's?? It would also be fantastic to produce the results for each Y, N, NK, and NA as a %. Each questionnaire contains approx 200 questions so I would like to be able to paste the formula into each result cell on the summary sheet. Any assistance gratefully received! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurance of text values across multiple worksheets
Help please Ardus.
I tried to enter the formulas that you used in your example but I don't know how to link the answer values to columns of the summary sheet. I don't understand the formula used. Could you explain for me so that I can re-enter? Many thanks Jiq. "Jiq" wrote: Thanks Ardus, I will give it a try in a bit. If it works like your example I will be totally grateful! May need additional help as I attempt it though as when I looked at the formulas you used I got lost! Thanks for your help. Jiq "Ardus Petus" wrote: I found a solution: linking the answer values to columns of summary sheet. Then you can perform calculations within summary sheet. Answer columns may be hidden in summary sheet. See example with 3 sites and 16 questions: http://cjoint.com/?ftmPAplI7b HTH -- AP "Jiq" a écrit dans le message de news: ... Sorry for any repetition with previous questions but I am completely hopeless with excel functions and got lost when attempting to use functions contained within previous postings. My problem: I have a questionnaire for 27 sites. For each site I have the completed questionnaire entered into a seperate worksheet i.e 27 worksheets. The answer for each question is "Y", "N", "NK", or "NA". I need to provide a summary of the 27 sites which I want to enter on a blank master questionnaire on a seperate sheet. Can anyone help with a formula that counts the number of "Y", etc for each question (the cell for each question is the same for each site on the different sheets). If possible, can the summary cell indicate the number of Y's, the number of N's, the number of NK's, and the number of NA's?? It would also be fantastic to produce the results for each Y, N, NK, and NA as a %. Each questionnaire contains approx 200 questions so I would like to be able to paste the formula into each result cell on the summary sheet. Any assistance gratefully received! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurance of text values across multiple worksheets
It is the same formula from F2 to H17. Excel automatically moves the
relative references to produce different references. For F2, the formula appears as: =INDIRECT(F$1&"!B"&ROW()) INDIRECT function has 1 string parameter, which must be a cell(s) reference. For instance: =INDIRECT("B12") produces the same result as =B12 F$1 resolves to the first cell of current column, where you find the name of the data worksheet ("Feuil2") ROW() returns current row (2) F$1&"!B"&ROW() concatenates the 3 elements and returns string "Feuil2!B2", which is the reference of the cell containing the answer to question #1 I used INDIRECT and ROW functions so that I can use the same formula throughout the whole array. I hope I have beeen clear enough You must replace my sheet names (feuil2, feuil3, feuil4) by the names of your individual site worksheets (if you have more than 3, you can extend the list to the right, then copy my formula to the newly created columns). If you're stuck, you can upload your workbook on http://cjoint.com , post back the link, and I'll be glad to fix it for you. HTH -- AP "Jiq" a écrit dans le message de news: ... Help please Ardus. I tried to enter the formulas that you used in your example but I don't know how to link the answer values to columns of the summary sheet. I don't understand the formula used. Could you explain for me so that I can re-enter? Many thanks Jiq. "Jiq" wrote: Thanks Ardus, I will give it a try in a bit. If it works like your example I will be totally grateful! May need additional help as I attempt it though as when I looked at the formulas you used I got lost! Thanks for your help. Jiq "Ardus Petus" wrote: I found a solution: linking the answer values to columns of summary sheet. Then you can perform calculations within summary sheet. Answer columns may be hidden in summary sheet. See example with 3 sites and 16 questions: http://cjoint.com/?ftmPAplI7b HTH -- AP "Jiq" a écrit dans le message de news: ... Sorry for any repetition with previous questions but I am completely hopeless with excel functions and got lost when attempting to use functions contained within previous postings. My problem: I have a questionnaire for 27 sites. For each site I have the completed questionnaire entered into a seperate worksheet i.e 27 worksheets. The answer for each question is "Y", "N", "NK", or "NA". I need to provide a summary of the 27 sites which I want to enter on a blank master questionnaire on a seperate sheet. Can anyone help with a formula that counts the number of "Y", etc for each question (the cell for each question is the same for each site on the different sheets). If possible, can the summary cell indicate the number of Y's, the number of N's, the number of NK's, and the number of NA's?? It would also be fantastic to produce the results for each Y, N, NK, and NA as a %. Each questionnaire contains approx 200 questions so I would like to be able to paste the formula into each result cell on the summary sheet. Any assistance gratefully received! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values | Excel Worksheet Functions | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
stopping pasted text deliminate across multiple cells | Excel Discussion (Misc queries) | |||
Functions across multiple worksheets | Excel Worksheet Functions |