I have a survey/form that was created in excel, and that is answered in excel. I am required to extract the data into a worksheet for each response received. I am able to pull the text that is entered, however i am having trouble pulling through the info from the checkboxes. So if a checkbox is ticked i cannot pull that through?
View 1 RepliesI have a series of checkboxes, we'll say checkbox 1-5. These are all listed on a sheet called products.
If for example you select check boxes 1,3 and 5 I need those to then be listed in a column in that order on another sheet called purchased.
If I went back and then uncheck 3 and check 4 then purchased would be updated.
I understand that if you take a cell and name it P1 for instance..and then on another sheet enter =p1 in a cell that they will update and share data, but I dont know how to pull from a check box..nor do I know how to assign a row of cells to accept multiple and changing information from those checkboxes.
After survey questions and responses are in excel, how do you analyze this data using a correlation matrix and regression analysis for reporting or testing hypotheses?
View 3 Replies View RelatedI want to analyze a survey which I made. Below you see an example of how I structured the answers.
Now I would like to rank the answes (rows 2-6) in a list according to often each item was mentioned.
I could do transpose the data manually and delete items which were mentioned various times. However, since it was a pretty big survey, it would take to long. Is there a formula to do it?
Original:
Person A
Person B
Person C
Water
Water
Sugar
Washing
Washing
Pasta
Boxes
Milk
Water
Frozen
Vegs
Fish
Cleaning
Cheese
Water
Example of how it should look like:
Water
3 (times mentioned)
100% (because everone mentioned it)
I did a survey of what kind of clothes people like to wear and why. So there are two things that people have to write down. ex) jeans and cus they're comfortable / shirts and cus they look good
The data that I received was organized as followed
I just put in random things just so you can get the idea of whats what
Id
jeans
shirts
cardigans
[Code].....
Okay so what I want to know is how I can get the responses from the data spreadsheet I showed you before into this organized data table above.
married, widows, single checkbox is selected
macro solution
good work.
I would like to analyze a survey form.
See attached example : survey.xlsx‎
I am trying to display a total of answers per question and transform that into a grid for easy reporting but am not sure how to do it.
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. How to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code:
Private Sub button_next_Click()
''confirm there is a name
If TextBox1.Value = "" Then
MsgBox ("Please enter your name")
[Code] ...
Here is the code in Module 1:
Code:
''global variables
Public info() As Variant
Public results() As Variant
Public questionnumber As Integer
Here is the code on the sheet that has the button to start the Survey Form:
Code:
Private Sub Start_Button_Click()
QASurvey.Show
End Sub
I am trying to rank questions within a survey with excel calculations and not VBA. The rank would take place for each individual section and would provide the top three right questions and top three wrong questions. The example below explains the ranking.
Brief facts:
Each survey has up to 30 sections.
Each section has up to 150 questions.
Each question has none or no limt of sub-questions.(usually between 1 and 20, weekly changes require this number to change)
The section's number of questions change weekly with updates.
This week section 2 begins with #12, next week begins with #16 because more questions were added to section 1.
There are three possible choices to answer a question (yes, no, or n/a.)
Sub-questions are checkboxes of explanations on why the question could be answered wrong.
Each question may have 0-10 points assigned.
Each sub-question may have 0-10 points assigned.
When a question is answered "no" is when a sub-question may be checked to help explain why.
A question with sub-questions may only be assigned points at the sub-question level. The sub-questions have the points and do not sum up to total the question value.
When a question is answered "no" and a sub-question is checked, the points received are 0 points for that sub-question. Unchecked sub-questions receive the full amount of points.
When a question is answered "yes" all points are received.
The Rank:
The rank would take place for each individual section and would provide the top three right questions and top three wrong questions at the question level. My example below has more than three to help explain further.
Select the highest actual score of question or sub-question. Start with 10 points to 0 points.
When a match is found that becomes the first "correct" question rank. #17 is 1 since 17.1 had an 8 point sub-question.
When there is a tie #14,15,18, the tie breaker is the sum of correct sub-questions added to the question score. #14 = 7 points.
The second sort is the # in ascending order #15 and then 18.
The hardest part of the rank is the flexibility of a changing survey with question/sub-question deletions and additions in each section. ...
I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.
If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.
I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.
How can I stop my subs from jumping from one to another?
Code:
Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Worksheets("TRF").Rows("36:41").Hidden = False
Worksheets("TRF").Rows("42:64").Hidden = True
Worksheets("TRF").Rows("65:76").Hidden = True
CheckBox19.Value = False
[Code] .........
how to open another thread. I would like to do a survey form (as attached) but I wanted to use VBA to make it easy to compute the result.
View 2 Replies View RelatedI'm creating a spreadsheet to collect survey data and how I have it set up is this:
Question 1 is a simple yes/no/maybe question - it uses an ActiveX spin button that the user presses up/down to increase/decrease the total. It looks like this:
Private Sub Yes_SpinUp()
With Range("C3")
.Value = WorksheetFunction.Min(15000, .Value + 1)
End With
End Sub
Private Sub Yes_SpinDown()
With Range("C3")
.Value = WorksheetFunction.Max(0, .Value - 1)
End With
End Sub
Cell C3 is where I keep track of how many people answered yes
The button is called "yes" under the button properties
So basically, I have one privatesub for spinning the value up and down. It works fine and is easily copy-paste-able for other answers, it just takes up a lot of space. I'm wondering if there's code I can write to handle the up/down in one sub. If not, i'll stick with what I have.
I have a list of vehicle number plates (or License plates for some of you) that have been recorded on a video camera and then converted to a text file using recognition software.
Unfortunately the records are far from 100% accurate so any data manipulation (travel times between points etc) becomes a bit dodgy and clumbsy to allow for this. I want to keep all of the data entries but need to identify each vehicle with a little more accuracy. I'm not worried about whether the plate number recorded is correct, only that it is the same for each instance that a particular vehicle is recorded.
The data comes in looking roughly like this:
ABC123
DEF456
AC13
A123
DE56
DEF56
I would like it to look like this:
ABC123
DEF456
ABC123
ABC123
DEF456
DEF456
Or even this:
AB123
DF45
AB123
AB123
DF45
DF45
It doesn't matter which of the above or any other compination as long as there are at least 4 characters.
The surveys are taken over up to a week so there are generally about 5000 records for each time slot that need to be processed.
I have tried to modify the Fuzzy matching functions with no success.
I have just recently finished inputting data into a spreadsheet which is a whole bunch of survey responses and I am trying to figure out the best way of now “analysing” it. Unfortunately the questionnaire consists of various types of questions, including some open ended, some where the participant selected a number on a scale, or the participants could select any number of options (e.g. tick any that apply).
From a brief search, I see that you can’t have two headings as such for a pivot table so I am wondering what the best approach might be. I have attached a sample spreadsheet. I have a hunch though that it’s going to be a matter of analysing each question individually and using filters and countif formulas (see attached).
An offset sumproduct series of functions provides me the total score percentage for the top level categories. There are 5-6 in total and can easily be charted in a radar.
The second step is to maintain the 'dynamic aspect' (being able to delete rows) and lookup the high-level category, and the subsection and return the score for the individual question. This way I can create a chart for each of the 5 top level categories and show the survey score for each answer in the category. Note each question has a maximum score of 5 and are scored 1-5.
I have a few errors looking up values and percentages coming back as 500% with the addition of new columns (in red in attached). The first sheet is error free, the second sheet is the 'build'.
Note: Charts will be housed on a separate sheet.
Is there a way to show the results of a survey (basic y/n) as a percentage of total surveys submitted?
I have also been charged with keeping the customer data (name/house number) visible when it's displayed as a chart?
Attached is a sample : sample.xls
Excel 2003 btw...
I have recorded the data from 12 records (i actually have hundreds, but im just using a small sample). Each record is represented by a vertical column. I want to find the total number of "x's" for only the records from Country1 (from all the answers). Then I want to find the number of "x's" from only Country2. I have tried the "countifs" function but cannot find a way to make it work. Would a different array function be better? How would I write it?
P.S. The answer for Country1 should be: 15.
The answer for Country2 should be: 5.
I have a spreadsheet which has a list of properties with a list of survey dates. The complication is that every property has multiple surveys and these are all on separate lines with the spreadsheet. The number of surveys could also be different depending on the property in question.
What I would like to do is to compile a report which only shows me when the latest particular type of survey (there are five types of survey, I'll call them 1, 2, 3, 4 and 5).
The survey type is shown in column C, the property is shown in column A and the survey date is shown in column F.
I conducted a survey and want to tabulate the results in excel. There is a lot of paper, so I want to list each answer on the spreadsheet and have a button next to it to tabulate the responses instead of doing it by hand. When I look at each survey, I want to click the button for the corresponding answer. I want to have excel tally/increment each response each type I click the button.
So what macro do I need to assign to each button in order to do this? Or can I just click on a cell and have it increment? Or what can I do to achieve this?
My attempts have given my circular reference errors, or if I do the iteration thing (tools, options, iterations), excel increments EVERYTHING in the spreadsheet by one instead of just one specific question.
I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).
My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.
I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
View 2 Replies View RelatedNot sure if this can be done, when I enter data for a journal only the data with the box checked is entered? I hope my example shows what I mean.
View 9 Replies View RelatedI have a macro which will copy a row of data from one sheet and paste it onto another sheet. I need some code to insert a checkbox on the left of every new row of data, (presumably something located in column A.)
View 3 Replies View RelatedHow can i hide and unhide one checkbox using another one? Can it be done using IF formula?
And also i am using this checkbox to function something else as TRUE/FALSE.
I currently have a macro which will copy a row of data from one sheet and paste it into the first blank row on another sheet "Invoices".
Now I want to write some code that will insert a checkbox to the left of each new row of data, (presumably in column A). I'm getting confused because I have never worked with checkboxes before and when I search the net for info. I'm getting some hits on Userforms (which I don't want), others on ActiveX controls and still others on form controls (what I think I need).
Here is some more info on what I'll be doing with them:
I am writing a second macro to see which boxes are checked. For any checked boxes I want the respective data to be sent to a mail merge and then the entire row, data and checkbox, to be deleted.
This adds a minor complication because if I delete a row, then all rows and checkboxes below it will need to move up. Is this something that will happen naturally or do I need to create some special code to handle that.
The final complication is that the data is data is going to sorted sometime between the creation of the checkbox and its later deletion. I'm wondering whether its necessary to keep a checkbox associated with a particular row, or if I can just tell the checkbox to figure out which row it exists at.
So, to sum up. I need to:
First - Learn how to create a checkbox
Second - Scan through all existing checkboxes and see which ones are checked
Third - Delete rows and sort the data while maintaining one checkbox for each row of data.
I need to take data from one sheet named "January" and paste it into another sheet named "Projections Sheet".
I want to use a checkbox to do this. So when the box is checked the data is entered into the next free row on the "Projections Sheet" starting in column B.
For example; I have data in "January" row 6, columns A through F. I need this data copied and pasted to "Projections Sheet" in the next free row (starting in row 6) and starting in column B.
Also, when I uncheck the box in January, I'd like the data removed from "Projections Sheet"
i am working on a spreadsheet that is doing two things 1) its allowing the user to choose between 1 to 3 number or N/A from the list validation option and 2) there is a checkbox if one selects that all the columns that have the formula will become N/A irrespect of data in it. I am using the following formula in the list validation option a3 is the check boc.
E5 =1
E6= 2
E7 = 3
E8=N/A
=if(a3=true, $E$8, $E$5:$E$8)
The issue I am having with this is, if I start selecting something from the drop down and half way I realize I need to check the box, it will only make it E8 value for the cells I have not touched and the ones I have already selected the drop down value it will remain. However I want it to override the value to E8 irrespect.
1) If the user of the spreadsheet clicks on the check box (this is in A3 cell) which i have linked it using format control then all the cell (Column E) that have the formula =if(A3=True, "N/A", ) will have N/A which is not applicable and not an error as you mentioned. I have successfully implemented this
2) If the user does not select the check box (a3) then each of the cell (Column E) has a drop down which the user can select either 1,2,3 or N/A. Which I have successfully implemented by using the data validations. Now the struggle I am having is as follows: I have attached the spreadsheet for your reference too.
Now if I select number 2 in cell E10, 2 in cell E11 and 3 in cell E 13 and you can see alll the other cell in column E are 0, because of the formula and the box in A3 is not checked in Tab 1. In Tab 2 I have checked the box everything turned N/A in column E except cells E10, E11 and E13 because I had previously selected 2,2,3 respectively. This is not what I want, what I want is when I check that box everything should turn to N/A basically an override function is what I want to created.
I have been using different keywords to find what I need. I have 3 checkboxes: cash, amex, other. I have a subtotal cell. When the appropriate checkbox is checked, the amount from the subtotal cell will auto go to the cell next to the checkbox. Is this VB?
View 6 Replies View RelatedI'm trying to create a dynamic msgbox that will display what data has been updated based on checkbox selections in the userform. I've named my checkboxes as Carey, Keith, and Juliet.
Ideally if only Carey's data has been updated, I'd like the msgbox to say
' Data has been Updated for:
- Carey '
If Carey and Keith's data has been updated, I'd like the msgbox to say
'Data has been Updated for:
- Carey
- Keith '
etc.
MsgBox ("Data has been Updated for:" & vbnewline & _
If CAREY.Value =true then "- Carey" End if & vbnewline & _
If KEITH.Value =true then "- KEITH" End if & vbnewline & _
If JULIET.Value =true then "- Juliet" End if & ")
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7