Help dig me out of a hole (XP/2000/97)

[Jezza]

You know the feeling when you have gone so far down the road that you realise that you can't go back, well that is how I feel.

A member of staff is currently doing a survey to 1100 staff at the offices where I work and has sent out a paper questionairre to them. She asked me to help her out with a method of quickly inputting the information onto her PC . She originally told me that there were about 50 questions with 5 options to ticks. Being a bit of a novice at excel VBA I said that I would put together an excel form which would output to an excel worksheet. The form uses optionbuttons which offsets each optionbutton value to the next active cell. I was then going to open a new worksheet and use the countif satement to count the number of each "True" answer in the respective columns. It works fine as there are 250 columns and just fits nicely across the sheet with 6 cells to spare...few.

Now comes the sad bit....

She neglected to tell me that there were in fact 7 options to each question, which now expands my row to 350 columns, which doesn't fit on a worksheet. No problem, I say, I will spread it over 2 worksheets, 2 groups of 25 question responses...works fine. Until her boss says to her "I want to do some demographic stats from the responses, not just counts" i.e. "The number of White-Irish who answered no to question 6"

This would work fine if I had been told all of this info and I would have probably gone down the road of creating a small access database, and it would have been easy for me if it had all been on one worksheet, but as the info is now spread across two sheets how can I append the data so that I can start interrogating it (anything in row 1 on sheet1 correspons to row 1 on sheet2 and so on).

[pieterse]

Hi,

<hr>She originally told me that there were about 50 questions with 5 options to ticks. <hr>

If each question can have only one answer, your sheet can be simplified and will only have 50-ish columns, right?

[Jezza]

Yep, that is what I thought at 12:01 this morning after I was changing the code.

But since my posting and a couple of cups of strong coffee, I reckon all is not lost , I have put together some multiple consolidation pivot tables together which seems to work. If my colleague comes up with the questions for interrogation I think this will be the answer.

I just don't want to have to redo the thing....time is money

[sdckapr]

Assuming you want 7 OPTIONS and not 7 radio buttons with only 1 possible response 1 - 7 (then use Jan's suggestion):

Instead of using 5 (or now 7) columns for 1 question (True with 6 falses) why not use only 1 column /question and input:
1 for option 1 + 10 for option 2 to 100 for option 3 + .... 10000000 for option 7

Then this value (eg): 1001010 would tell you that options 7, 4, and 2 were selected.

Steve

[Jezza]

Yep, I like the idea but I was looking for ease of use for my client for inputting. They are only allowed one response per question as the scores are weighted so I have grouped the option buttons for each question. Apparently they are getting in a temp to do the inputting and they will just have to click with the mouse to the relevant option selected on the paper form. Could there be error creaping in when we ask a person to enter long strings of 1's and 0's? Just a though, or have I not understood your response?

[sdckapr]

If there is only 1 option available you only need to use radiobuttons and get the number 1-7 to store in 50-some columns.

This is academic to your needs, but might be useful to someone else doing something similar:
My way is if they are ALL options (you can select MULTIPLE items). I was thinking that the the one/zero "values" would be generated from the userform based on the selections not entered by users. After checking/unchecking the 7 possible options, the "value would be generated via code (1 for checked, 0 for unchecked) * each "place" = 10^Question number. This Calculated value would be entered into a cell and used however you needed it.

Steve

[Jezza]

Thanks for your help on this. I have learnt a very big lesson on this exercise. I think it is called the 7 P's, Proper Planning and Preparation Prevents <img src=/w3timages/censored.gif alt=censored border=0> Poor Performance.

[pieterse]

<hr>7 P's<hr>

<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[Servando]

Hi Jerry:

I would like to know the seventh P, in order to enrich my poor english

servando.villalon@desc.com.mx

[Jezza]

I may be breaking Rule 18 if I publish this word, but I will put in code for you:

1) If an English man drinks too much beer he gets <img src=/w3timages/censored.gif alt=censored border=0>
2) It rhymes with the noise a snake makes
3) Sometimes when an American gets annoyed they may say " That really <img src=/w3timages/censored.gif alt=censored border=0> me off"
4) It rhymes with the word for, when two sets of lips touch each other

have I given you some clues?

[Servando]

Very explicit Jerry, thanks.

Saludos/Regards

[LegareColeman]

Another possibility would be the place that Woody lives in Thiland.