# 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

#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.MIKEhttp://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.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 / count1End SubPrivate 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).ValueEnd FunctionPrivate 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 IfEnd 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 RangePercentage = "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 SubPrivate 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 IfEnd Sub

Report •