I have an Excel worksheet, let's say a Check List template

August 27, 2013 at 03:58:53
Specs: Windows 7
The Check List contains a list of subject(items) ),and there are the following columns named (20%,40%,60%,80%,100%, Not Required), the idea is that the user wants to select the percentage of the interested topics by check or enter for example a cross sign in the cell and after that i want to make a function that checks that loop through each row to check that there is only one entry, For example if the user selects for the same topic or put a sign cross inside the cell which corresponds to the percentage Column 20% and then he puts another check inside the percentage column 40%, then the previous check must be removed from the column percentage 20% and the same for whole rows.

Can anybody help Please ? Any help is appreciated


See More: I have an Excel worksheet, lets say a Check List template

Report •


#1
August 27, 2013 at 17:15:36
It would appear that to do what you want, in the manner you want, you will need a Macro.
But why not just have your Topics in Column A and in corresponding cells in Column B have drop-down lists with the percentages?

No need for checking marking a third cell.

MIKE

http://www.skeptic.com/


Report •

#2
August 29, 2013 at 05:13:47
Yes, I am using Macro in order to solve this problem, but the problem when i want to make line check because the user is not allowed to choose more than one percentage that means he will assign a single cross sign per row, but the question is how to know the when you reach the end of each row. The other thing is when ever the user selects a percentage for the interested topic and he want to change it, i want to remove the previous one.. there is a logic behind this but i don't know.

What you suggested above is really easy way to achieve what i want, because in this way i will not need to check for a line or search for how many cross signs do i have i the row, it's just loop through the whole column B, and calculate the average.

I would like to thank you for help


Report •

#3
August 29, 2013 at 05:47:49
I have put together a very quick macro that will check each row (topic) and go through the various columns (from B to G) and will check for more than 1 entry. If more than one is found, it will then remove the second entry, for example, if 20% and 60% are selected, it will remove the 'x' from 60%.

The Data i am using is:

	20	40	60	80	100	Not Required
Topic 1						
Topic 2						
Topic 3						
Topic 4						
Topic 5						
Topic 6						
Topic 7						
Topic 8						
Topic 9						
Topic 10						


The code i have written is:

Dim URange, LRange
Dim BCell As Range
Dim nCounter, RowPlaceholder

Sub CheckValues()

    Set URange = Range("A2")
    Set LRange = Range("A" & Rows.Count).End(xlUp)
    
    For Each BCell In Range(URange, LRange)
        
        For i = 1 To 6
            If BCell.Offset(0, i) <> Empty Then
                nCounter = nCounter + 1
                RowPlaceholder = BCell.Offset(0, i).Address
                   
                    If nCounter >= 2 Then
                        Range(RowPlaceholder).Clear
                        RowPlaceholder = Empty
                        nCounter = 0
                    End If
               
            End If
        Next i
        nCounter = 0
    Next BCell
            

End Sub

this is by no means perfect but it is a starting point i guess, have a go and let us know how it works and i am sure we can try to help further.


Report •

Related Solutions

#4
August 29, 2013 at 07:39:41
One simple way to prevent double entries in a Row is to use the WorksheetChange event to only allow one entry per column per row.

Using AWTL's layout above (which I assume is in A1:H11) this code will prevent the entry of a character in more than one column of Rows 1:11, Columns B:H. If an entry is made in more than one column of a given row, only the latest entry will be retained.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made within the Percentages columns
 Set isect = Application.Intersect(Range("B2:H11"), Target)
   If Not isect Is Nothing Then
'Disable events
    Application.EnableEvents = False
'Save the character entered
      myChar = Target.Value
'Clear the row
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "H")).ClearContents
'Place the character in the cell
      Target = myChar
'Enable events
    Application.EnableEvents = True
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 29, 2013 at 07:43:36
Tried DerbyDads code and its much better than mine :(


Report •

#6
September 2, 2013 at 00:18:45
Thank you very much, Actually both replies were really help :), but if i need to calculate the average, and for doing this i need to loop through all the cells to count the cross signs for the selected topics, but i need also to know where thery are located in order to count and multiply by the percentage column, for example if i have 3 cross signs in the Percentage-Column 20 then 3*20 and 5 in Percentage-Column 60 then 5*60 and the same way for the rest of the columns, Finally adding all the result together and divided by the number od the selected topics in order to get the average.

Thank you for the help, actually i need to do this in the easiest way but i am not familier with Macro. :(


Report •

#7
September 2, 2013 at 07:31:27
You don't need a macro to find the average, you can do it with formula(s).

For example, assume this data set:

	
                   B   C   D   E     F
1                20 40  60  80  100  Not Required
2  Topic 1						
3  Topic 2						
4  Topic 3						
5  Topic 4										

In B6 enter =COUNTA(B2:B5)*B1 and drag it across to F6.

After that, you can whatever you want with those values. You also use one long formula in a single cell by combining a bunch of COUNTA formulas for each column.

Further, you could wrap the entire formula with an IF function that won't calculate a value until the proper number of X's have been entered.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
September 11, 2013 at 03:23:15
this my code that ..in the function Count_Selection() it loops over the range of cells and then it calls the worksheet_change in order to check the cross sign per line to make sure the user doesn't choose more than one percentage and count and i have to call another function which is GetPercentage to know where is the cross sign is located (in which column ) then returns the columns number which indicates the percentage back to Count_Selection to calculate the average...

Const Percentage = "D6:I100"
Const CurrentSheet = "Check List"

Private Sub Count_Selection()
Dim averagePercentage As Long
Dim count1 As Integer
Dim total1 As Integer
Dim cell As Range

For Each cell In Worksheets(CurrentSheet).Range(Percentage).Cells
' check for the cross sign 'X'
Worksheet_Change (Percentage)
count1 = count1 + 1
total1 = count1 + GetPercentage(cell.Column)
Next cell
averagePercentage = total1 / count1
End Sub


Private Function GetPercentage(ByVal columnNumber As Long) As Double

If columnNumber = 4 Then

ElseIf columnNumber = 5 Then

ElseIf columsnumber = 6 Then

ElseIf columsnumber = 7 Then

ElseIf columsnumber = 8 Then

End If

GetPercentage = Worksheets(CurrentSheet).Cells(1, columnNumber).Value
'GetPercentage = Cells(1, columNumber).Value

End Function

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made within the Percentages columns
Set isect = Application.Intersect(Range("D6:I100"), Target)
If Not isect Is Nothing Then
'Disable events
Application.EnableEvents = False
'Save the character entered
myChar = Target.Value
'Clear the row
Range(Cells(Target.Row, "D"), Cells(Target.Row, "I")).ClearContents
'Place the character in the cell
Target = myChar
'Enable events
Application.EnableEvents = True
End If
End Sub


Report •

#9
September 11, 2013 at 03:27:44
I want to know in the GetPercentage function what action i have to do inside the if statements to return the right column number in order to calculate the average because it depends where is the cross sign located to multiply the number of cross signs that we have in the same column by the percentage and the same for all, then find the average.


Report •

#10
September 11, 2013 at 03:28:53
Thank you all for any help, all appreciated :)


Report •

#11
September 12, 2013 at 02:22:24
Here is my Macros, I have developed it by another way but I got an compiler error: Invalid outside procedure.

Dim Percentage As Range
Percentage = "D6:I100"
Const CurrentSheet = "Check List"

Private Sub Count_Selection()
Dim averagePercentage As Long

Dim count As Integer
Dim total As Integer

Dim count1 As Integer
Dim sum1 As Integer

Dim count2 As Integer
Dim sum2 As Integer

Dim count3 As Integer
Dim sum3 As Integer

Dim count4 As Integer
Dim sum4 As Integer

Dim count5 As Integer
Dim sum5 As Integer

Dim cell As Range

For Each cell In Worksheets(CurrentSheet).Range(Percentage).Cells
' check for the cross sign 'X'
Worksheet_Change (Percentage)

count = count + 1

If cell.Column = "4" Then ' cross sign 'X' located in Column Percentage 20%
count1 = count1 + 1
sum1 = count1 * 0.2
ElseIf cell.Column = "5" Then ' cross sign 'X' located in Column Percentage 40%
count2 = count2 + 1
sum2 = count2 * 0.4
ElseIf cell.Column = "6" Then ' cross sign 'X' located in Column Percentage 60%
count3 = count3 + 1
sum3 = count3 * 0.6
ElseIf cell.Column = "7" Then ' cross sign 'X' located in Column Percentage 80%
count4 = count4 + 1
sum4 = count4 * 0.8
ElseIf cell.Column = "8" Then ' cross sign 'X' located in Column Percentage 100%
count5 = count5 + 1
sum5 = count5 * 1

End If

Next cell
total = sum1 + sum2 + sum3 + sum4 + sum5
averagePercentage = total / count

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made within the Percentages columns
Set isect = Application.Intersect(Range("D6:I100"), Target)
If Not isect Is Nothing Then
'Disable events
Application.EnableEvents = False
'Save the character entered
myChar = Target.Value
'Clear the row
Range(Cells(Target.Row, "D"), Cells(Target.Row, "I")).ClearContents
'Place the character in the cell
Target = myChar
'Enable events
Application.EnableEvents = True
End If
End Sub


Report •


Ask Question