Dynamic form feeding a database....

[Gids007]

Hi There,

We are trying to use excel in several ways. We have a survey that our staff fill out, a series of questions some are answered yes or no, some are answered on a scale from 1 to 5. The questions are different for each role.

We currently have a workbook with a sheet for each role. The staff member selects the appropriate sheet, answers the questions, saves it with their name as a reference.

So, some poor person has to collate 200 separate spread sheets so we can see a aggregated view of the answers.

I want 1 sheet that will display the right questions depending on the role selected. Once the user has answered the questions I want that data, plus data about the user (name, role, branch etc), to then be appended to the existing data. I am happy if this stays in excel as we use tableau to aggregate it.

Any help, much appreciated.

 

[My Aswer Is This]

Things like this can be done.

You said:

We currently have a workbook with a sheet for each role​

So you could have a sheet named Question.

And in Range("A1" you have a Data Validation list with the different roles
Like Math
Like Science

Then if you choose Math

A list of question about Math would show up in Range("A2") and below for all the questions

Or if you choose English question about English would show up

And the user would enter answers in column B next to the question in Column A

When user was finished he would Double click on Range("B1")

And the data would be copied over to a sheet Named Master

Or something like this.

You will have to explain more about how you might want something like this setup

I gave one example.

My Example would require using Vba

 

[Gids007]

Thank you for this...I'll have a crack at the first part and then think about he VBA bit...I'm not a VBA person unfortunately..

Do you have any examples you are happy to share of how the question selection would work?

 

[My Aswer Is This]

Here is a example:

Assuming you enter manually into Range("A1")
Or choose from a Data Validation list which is best

Choose or Enter English or Math

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:B" & Lastrow).Clear
If ans = "Math" Then
    With Target
        .Offset(1).Value = "What is Your Name"
        .Offset(2).Value = "What is Math"
        .Offset(3).Value = "Do you love Math"
        .Offset(4).Value = "What is 10 times 20"
    End With
End If
If ans = "English" Then
    With Target
        .Offset(1).Value = "What is Your Name"
        .Offset(2).Value = "What is English"
        .Offset(3).Value = "Do you love English"
        .Offset(4).Value = "What is a Noun"
    End With
End If
End If
End Sub

 

[Gids007]

Brilliant thanks.

 

[My Aswer Is This]

So now we would need to know where to copy this data too after completion.

I can have the script copy the data to another sheet when you double click on B1

But I need to know what sheet to copy to:

And would we enter it in Column A and B

Question in column A answer in column B

 

[Gids007]

Yes, Question in A and Answer in B...thanks you

 

[My Aswer Is This]

Ok.

This script assumes
Your sheet with the questions is named "Questions"
The sheet you want the answers copied to is named "Master"

And when you enter into Range("A1") of sheet named "Questions or select from a Data Validation list the word Math or English The script will run.

Now when your ready to copy data to sheet named "Master"

Double click on Range("B1") in sheet named "Question"

This data will be copied to sheet named "Master"


Install this script into sheet named Questions like explained in earlier Post.


Now you do understand you can modify script to use other subject add as many as you want.
I suggest using a Data Validation list so user does not enter wrong data

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/13/2019  12:25:09 AM  EST
If Target.Address = "$A$1" Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
Dim Lastrow As Long
Lastrow = Sheets("Questions").Cells(Rows.Count, "A").End(xlUp).Row
Target.Offset(1).Resize(Lastrow, 2).Clear

If ans = "Math" Then
    With Target
        .Offset(1).Value = "What is Your Name"
        .Offset(2).Value = "What is Math"
        .Offset(3).Value = "Do you love Math"
        .Offset(4).Value = "What is 10 times 20"
    End With
End If
If ans = "English" Then
    With Target
        .Offset(1).Value = "What is Your Name"
        .Offset(2).Value = "What is English"
        .Offset(3).Value = "Do you love English"
        .Offset(4).Value = "What is a Noun"
    End With
End If
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified  2/13/2019  12:25:09 AM  EST
If Target.Address = "$B$1" Then
Dim Lastrow As Long
Lastrow = Sheets("Questions").Cells(Rows.Count, "A").End(xlUp).Row + 2
Dim Lastrowa As Long
Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 2
Range("A1:B" & Lastrow).Copy Sheets("Master").Cells(Lastrowa, 1)
End If
End Sub

 

[Gids007]

Thank you so much, this works a treat...One issue with the data validation...it disappears after I use it once.

Apart from that it's perfect...thanks

 

[My Aswer Is This]

Yes I noticed that later. If you try the two scripts I provided in post 8 this problem you mentioned should not happen.

Do not use script provided in post 4

Let me know if post 8 works for you.