Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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).
    Jerry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    Jerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Jerry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <hr>7 P's<hr>
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi Jerry:

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

    servando.villalon@desc.com.mx

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Jerry

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Very explicit Jerry, thanks.

    Saludos/Regards

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •