Solved

Initial to Target Conditional Selection of Question Answer

Posted on 2011-03-20
18
217 Views
Last Modified: 2012-05-11
I have a sample spreadsheet ("Initial") that was done by EE that limits the number of sequential answers (in this case "False") that can occur, before shutting down the rest of the answers in a particular category.  This spreadsheet works perfectly.  I have however, been trying to incorporate the functionality in my production sheet.  The problem is, the columns don't line up and when I try to incorporate the logic in my production ("Target") sheet, I get several errors.  In addition to combining the Private Sub Worksheet_Change(ByVal Target As Range) statements as I add the Initial to the Target, I've got to align the columns and I continue to get a "variable not defined" error.

Here's what I need.  I need someone to look at the Initial Sample and understand what is going on.  The model is currently set for 3 consecutive "False" answers to shut down any additional selections per category.  Then, look at the Target Sample and see how to incorporate the same functionality (with variable selection) in that model.  The result should be a Target model, that when X # of "Falses" are reached, it shuts down BY CATEGORY.  That is to say, the next category, starts the counting of "False answers" over.  Ignore all the Modules.... I did not take them out in the Target Sample.

Thank you in advance,

B. Initial-Limit-to-Answers.xlsm
Target-of-Limit-to-Answers.xlsm
0
Comment
Question by:Bright01
  • 10
  • 8
18 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35180168
Bright,

Thanks for using the related question, otherwise I may not have noticed your problems in getting it to work...
Give me a couple of minutes, and i'll help you out.
0
 

Author Comment

by:Bright01
ID: 35180255
Much thanks!  You can probably see how tough it is for me to match up the logic.

B.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35180465
This should be it.

I copied the original macro contents to their new location and updated the cell references to take into account the different categories and their cell placement.

 updated-Target-of-Limit-to-Answe.xlsm

Before you can use it however, you need to put the consecutive number of falses somewhere in a cell. I have preset the formulae to use the value 3 , but ideally this should be a cell, such as eg. [E2]. In order to update the worksheet, a password is required which I do not posess.
'=========================================================
                For response = 1 To 3 'Range("E2")
'=========================================================

Open in new window

0
 

Author Comment

by:Bright01
ID: 35183420
Akoster,

Thanks for the reply and code!  I'm testing it now.  When you say I need to have the number of Falses in a cell, I have place them in a cell outside the Q & A; and it seems to work with regard to limiting the number of falses, however, take a look at the cell next to the cell that cannot be inputed and the subsiquent (within category that are then limited) CELLS.  You should not be able to input anything into them either once the threshold is reached.  BTW; the range should be from 1 - 15 Falses entered stops the next entry with 15 meaning there is no "governor" on the entry.  I could however, live with 1 to 5 (falses) halt entry.  

BTW; password is JAM.

Thank you!

B.
0
 

Author Comment

by:Bright01
ID: 35189239
Akoster,

I'm still testing the macro and it's very good. However, try skipping one of the questions; you can still answer questions down the category.  By giving you the Password, can you make the slight modeifications to include the cell reference?

Thank you,

B.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35190588
Bright,

Nice to see that it works out !

I have placed the "number of falses" limit in cell E1, and included this in the macro code.
By using the same color text as the background, it should be invisible when viewing or printing. Just to be sure, I set the font size to 1.


update-2-Target-of-Limit-to-Answ.xlsm
0
 

Author Comment

by:Bright01
ID: 35194679
Akoster,

This may require some testing on your end.  Here's what I have.  I see where if set to three, on the third false, it prevents a third false.  When set to three, it should allow three falses, then prevent ANY ADDITIONAL INPUT IN ANY OF THE CELLS left in the category (1-15) including input in the cells in column D.  Cells in C and  D that are prior to the limit being reached may be changed but not any data elements beyond the threshold in C or D.  

Let me explain; When a question is asked, we want to limit the number of false answers before shutting down the ability to add any additional answers.  This saves both time and frustration since the questions tend to build on one another.  So if someone answered X number of questions False, we want to shut down the category and move on to the next.  Make sense?

Also, I'm getting a "Type MisMatch" error; not sure what that is.

Take a look,

And "thank you" in advance.

B.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35199318
B,

So if I do understand you correctly, you want to start reading the answers, and after the third (when number of falses is set to 3) false answer (no matter how much partial or correct answers are in between) you want to block any changes in columns C and D for all questions that have not been answered yet.

this differs from how I interpreted the starting post, where a sequential (consequtive) number of false answers is mentioned.
0
 

Author Comment

by:Bright01
ID: 35200640
Yes, sequential and "false" stops category input. A true or partial restarts the count.  New Category starts count over also.  Yes; C & D are blocked when count "false" is reached...

B
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Expert Comment

by:akoster
ID: 35207743
Thank you for your response, it's all clear to me now.
I couldn't get the "a true or partial result restarts the 'number of falses' count" out of the prior information !
0
 

Author Comment

by:Bright01
ID: 35213494
Sorry.  I must say, I love EE for both the results and the interaction with smart and talented people. Sometimes these macros are rather difficult to understand and the requirements are always a challenge to explain.  However, the tenacity of most of the EE Professionals is unbelievable.  My membership to EE is the best dollar I spend every month.  Thanks again for hanging in on this.

B.
0
 

Author Comment

by:Bright01
ID: 35231704
Akoster,

Any update?  Much thanks,

B.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35233400
I got it working, it actually became somewhat simpler :
Private Sub Worksheet_Change(ByVal Target As Range)
'This macro automatically populates a To/Be cell with Achieve thus auto including the value
'for the maturity model.  Also it sorts the priority questions in the Priorities tab so that
'you can quickly see which questions have the most value to the client
'Finally, it makes sure that after a number of consecutive false answers, any additional
'selections are shut down. This check is performed for each category
'

Dim i As Integer, j As Integer, k As Integer
Dim valid As Boolean
Dim response As Integer
Dim checks As Integer
Dim number_of_falses As Integer
    
    On Error GoTo errHandler
'-- disabling screenupdating prevents visual flicker when running this subroutine.
'-- pay attention that it is enabled again !
'    Application.ScreenUpdating = False

'-- disabling events prevents events from happening when the change event is processed.
Application.EnableEvents = False
    
    '~~> populate To/Be cells
    If Target.Column = 3 Then
        For r = 4 To Range("C65536").End(xlUp).Row
            If Cells(r, 3).Value = "True" Then Cells(r, 4).Value = "ACHIEVE"
        Next r
    End If
        
    '~~>  consecutive falses
    If Not Intersect(Target, Range("C3:D219")) Is Nothing Then
        
        '-- verify correct target value
        Select Case Target.Row
        Case 6, 24, 42, 60, 78, 96, 114, 132, 150, 168, 186, 204
        Case Else
            If Len(Trim(Target.Offset(-1).Value)) = 0 Then Target.Value = ""
        End Select

        '-- determine the rows that are to be checked for this category
        Select Case Target.Row
        Case 3 To 20
            '-- cat 1
            j = 20: k = 6
        Case 21 To 38
            '-- cat 2
            j = 38: k = 24
        Case 39 To 56
            '-- cat 3
            j = 56: k = 42
        Case 57 To 74
            '-- cat 4
            j = 74: k = 60
        Case 75 To 92
            '-- cat 5
            j = 92: k = 78
        Case 93 To 110
            '-- cat 6
            j = 110: k = 96
        Case 111 To 128
            '-- cat 7
            j = 128: k = 114
        Case 129 To 146
            '-- cat 8
            j = 146: k = 132
        Case 147 To 164
            '-- cat 9
            j = 164: k = 150
        Case 165 To 182
            '-- cat 10
            j = 182: k = 168
        Case 183 To 200
            '-- cat 11
            j = 200: k = 186
        Case 201 To 218
            '-- cat 12
            j = 218: k = 204
        End Select
        
        '-- check for a number of consequtive false conditions
        number_of_falses = 0
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                Select Case Range("C" & i).Value
                    Case CStr(False)
                        number_of_falses = number_of_falses + 1
                    Case CStr(True), "PARTIAL"
                        number_of_falses = 0
                End Select
                
                If number_of_falses > Range("E1") Then
                    Target.Value = "n/a"
                    GoTo errHandler
                ElseIf number_of_falses = Range("E1") Then
                    If Target.Column = 4 Then Target.Value = "n/a"
                End If
            End If
        Next
    End If

    '~~> Check if the change is happening in B12:B26
    If Not Intersect(Target, Range("D3:D219")) Is Nothing Then
        '~~> To check for rest 3 conditions
        If Target.Offset(, -1).Value = CStr(True) Or Target.Offset(, -1).Value = "N/A" Or Len(Trim(Target.Offset(, -1).Value)) = 0 Then Target.Value = ""
    End If
   
    
errHandler:
    If Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Open in new window




 update-3-Target-of-Limit-to-Answ.xlsm
0
 

Author Comment

by:Bright01
ID: 35305276
Akoster,

I am very sorry it has taken me some time to get back with you.  I have been "slammed" with work over the past three weeks.  However, given it is Saturday, I have gotten back into this.  I tested the code and we still have several simple problems.  And most of all, I really appreciate you hanging with me on this.  So here's what I've learned;

If you go to the first Category (CATEGORY1) and select three FALSE answers in Column C you will find that when you select the forth FALSE it does not let you do that ... this is correct. HOWEVER, if you select TRUE or PARTIAL in the next cell, you can then select another FALSE and so on.  What it should do is not let you enter any more answers in any of the cells below the third FALSE regardless of what you select.  This would mean ANY answers for cells C or D WITHIN THAT CATEGORY.  The FALSE count starts over on the next Category.

Next,

If you select 3 FALSES in a row, and don't answer in the corresponding cell "D", for some reason you can't go back and put answers in the corresponding Cells in "D".  You should be able to do that in all corresponding cells that have answers (in "C"), even after the macro/formula has shut down any additional answers to the cells below the third FALSE.  

Finally, a small problem..... if you push the "Clear" button we get a "Type Mismatch".... you must push OK 13 times to eliminate it.  I think this is a simple problem somewhere in Module 1 but I can't find it.

Hopefully this is it in terms of work for you.  Again, you have been most kind to help me out on this.

Very appreciated,

B.
0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 35313372
No problem, the last weeks have been very busy for me too.

I seem to have overlooked that, the original approach was to start checking at question 15, working up. The better way is to do this the other way around, which is done in update 4 (see below).

For the 12 error messages :
the problem is in this code fragment
    '~~> Check if the change is happening in B12:B26
    If Not Intersect(Target, Range("D3:D219")) Is Nothing Then
        '~~> To check for rest 3 conditions
        If Target.Offset(, -1).Value = CStr(True) Or Target.Offset(, -1).Value = "N/A" Or Len(Trim(Target.Offset(, -1).Value)) = 0 Then Target.Value = ""
    End If

Open in new window

The offset function does not work when target is a range and not a single cell. Because the purpose of this code is not clear to me, I suggest commenting this part out.


update-4-Target-of-Limit-to-Answ.xlsm
0
 

Author Comment

by:Bright01
ID: 35313582
Akoster,

This is great!  I haven't tested it fully, but in the first pass it seems to work very well.  You've done a great job on this; and no simple task.  I hate to scope creep anything but I was wondering, is there a way to Turn this capability ON/OFF by indicating via either an ActiveX "button" or a "YES/NO" or "check box" in a cell?  Also, can I move the Governor # (in this case "3") to another position by cut/past or drop/drag, without changing the code (i.e. does Excel follow the logic in the code of changes to a cell's location)?

Much thanks; and I'm happy to issue a follow on question if these answers are going to take any time.

Best regards,

B.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35313920
a "follow on" question for the on/off functionality would be nice, not so much because of the time taken to answer them, but rather to enable others having similar problems to find their way efficiently.

the 'governor number' can be put anywhere, just make sure to update the reference from the code to match the new situation.
To have maximum flexibility in this, you can replace _ALL_  items of
Range("E1")

Open in new window


to

Range("Governor_number")

Open in new window

.

Then select the E1 cell (containing the number 3), and assign it the name "Governor_number" by typing this in the name box (the left part of the formula bar, white field with text "E1") or by navigating to the names section in the 'insert' or 'formula' menu sections and linking the name "Governor_number" to the cell E1.
0
 

Author Closing Comment

by:Bright01
ID: 35322581
Great work!  Thank you for sticking with this effort.  Not an easy fix but you did a great job with the logic and coding.

Best regards,

B.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now