Data validation to check for normal distribution

[deedar]

I have an Excel Workbook in which list of support, technical and administrative staff is entered. There is one sheet for each employee. Sheet name is same as employee name. Each sheet contains list of all support, technical and administrative staff. There is also an introduction sheet.

When the workbook is opened, it asks for a password and based on password, Introduction Sheet and relevant employee’s sheet become visible. (To accomplish this, I have used the technique mentioned in this thread; thread68-924496 )

Columns in an employee’s sheet are as follows:

A = Employee Number
B = Name
C = Job Title
D = Staff Category
E = Behavior Rating
…
…
I = Behavior Rating %

Column E contains Data -> Validation -> List
(Source = Ratings (Values can be A, B or C))

Cells I3, I4 and I5 have the following formulas:

I3 = CEILING(((COUNTIF(E:E,"A")/(COUNTA($D$3:$D$40)-1)))*100,1)
I4 = CEILING(((COUNTIF(E:E,"B")/(COUNTA($D$3:$D$40)-1)))*100,1)
I5 = CEILING(((COUNTIF(E:E,"C")/(COUNTA($D$3:$D$40)-1)))*100,1)

What I need to achieve is when an employee enters ratings for other staff in column E, Ratings A and C should not exceed 30% and Rating B should not exceed 40%.

 

[SkipVought]

Hi,

And you cannot write a formula that expresses that requirement?

Have you tried? What have you tried?

PS: I'd advise against having one sheet per data item. This multiplies maintenance costs.

ONE data source.

One query, with the Employee as the criteria (similar procedure to your password thing)

The employee sees their data.

Changes update the one data source.

Just MHO.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[deedar]

Thanks.

I tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 5 Then
    Exit Sub
ElseIf Target.Value = "A" And Range("I3") > BehaviorARating And Range("I6") <= 100 Then
    MsgBox "Rating A cannot exceed 30%"
    Target.ClearContents
ElseIf Target.Value = "B" And Range("I4") > BehaviorBRating And Range("I6") <= 100 Then
    MsgBox "Rating B cannot exceed 40%"
    Target.ClearContents
ElseIf Target.Value = "C" And Range("I5") > BehaviorCRating And Range("I6") <= 100 Then
    MsgBox "Rating C cannot exceed 30%"
    Target.ClearContents
Else
End If

End Sub

I had to copy-paste this code in every employee’s sheet.

I6 = SUM(I3:I5)
Range("I6") <= 100 is written to cater for round-off error.

Your advice against having one sheet per data item is far better than my working. I will keep this in mind for my future Excel work.

 

[SkipVought]

Although it is a common practice in my company to produce multiple workbooks, worksheets, charts, etc. for reporting, I will, almost always, have one workbook per application, with one interactive sheet or interactive chart for reporting similar information.

Do you still have an unresolved issue? I did not see a question.

Skip,

_{Just got a nuance...
to replace the old subtlety!}

 

[deedar]

Thanks Skip.

The above code worked in my scenario. But I am having one issue. The file is taking too long to save. I made sample entries in one sheet and then clicked x (Close Window) on top right to close the file. The following code is written for BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wks As Worksheet
ActiveWorkbook.Unprotect ("abcd123efg")

 For Each wks In ActiveWorkbook.Worksheets
  If wks.Name <> "Introduction" Then
    wks.Visible = xlVeryHidden
  End If
 Next wks

With ActiveWorkbook
   .Protect Password:="abcd123efg"
   .Save
End With

End Sub

I calculated the time using my watch and the file took 34 seconds for closing. I used F8 to step through the code and much of the time is taken by the line:
.Save

There are 40 sheets in this workbook. Size of the file is 616 KB.

 

[SkipVought]

In Tools > Options - Calculation Tab, do you have Recalculate before save checked?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[deedar]

Yes, Recalculate before save is checked and Calculation is set to Automatic. I selected Manual, cleared Recalculate before save and then set the Calculation again to Automatic. This did not make any difference.

I have defined six named ranges. These ranges are referring to the cells of a worksheet of this workbook and these names are being used by 38 sheets. May be, this would have increased the saving time …