Auto Calculate Based on cell meeting a condition

[thedeadzeds]

Hi All,

Not sure if this is at all possible but if anyone can figure it out you can...

I have a very basic spreadsheet which consists of 2 worksheets, a main sheet that is completed by staff, and then a summary sheet.

The main sheet shows the following:

a list of staff names in one column - next to each staff is a list of cases. Each staff member requires a Full Audit of one in ten cases. So the main spreadsheet shows 'Full audit' which is then followed by 9 sense checks. Once 9 ten sense checks have been done, another 1 full audit is required and then we can release another 9 sense checks.

The senses available sheet shows the following:

A summary of each staff member and how many sense checks are available before it hits 9 and then another full audit is required.

The senses available sheet is currently manually updated but I wonder if this can be automated. I have attached a copy of the spreadsheet. The senses available sheet currently shows the correct number of sense available which has manually been updated.

Hope this makes sense.

Thanks in advance
Craig

[Bob Phillips]

Try this worksheet event cde

Private Sub Worksheet_Change(ByVal Target As Range)
Const kFull As String = "Full Audit"
Dim startCell As Range


    If Target.Count > 1 Then Exit Sub
    
    If Intersect(Target, Me.Range("K4:S8")) Is Nothing Then Exit Sub
    
    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    With Target
    
        If .Value = "Sense" Then


            If Not IsError(Application.Match(kFull, Me.Rows(.Row), 0)) Then
            
                Set startCell = Me.Cells(.Row, Application.Match(kFull, Me.Rows(.Row), 0))
            Else
            
                Set startCell = Me.Cells(.Row, "C").Offset(0, 1)
            End If


            If Application.CountIf(startCell.Resize(, .Column - startCell.Column + 1), .Value) = 9 Then
            
                .Offset(0, 1).Value = kFull
            End If
        End If
    End With
    
ws_exit:
    Application.EnableEvents = True
End Sub

[thedeadzeds]

Many thanks for this bob, unfortunately I cannot get this to work. Is there anyway you could show me how it works in the attached spreadsheet?

Kind regards

[Bob Phillips]

Try this

[thedeadzeds]

Thanks Bob, I'm probably doing something wrong but it doesn't appear to work. The Senses Available worksheet still doesn't collate the data automatically. Many thanks for trying anyway,

Craig,

[Bob Phillips]

You need to articulate the problem better. I gave you a spreadsheet that works (as far as I understand your requirement). When you say you cannot get it to work, a) what do you mean by 'get it to work', have you changed it in anyway, and b) explain what is happening with this file I gave you.

[thedeadzeds]

Hi Bob,

Sorry if my explanation is not very clear. I'll try again:

I'm trying to get the "Senses Available" sheet to automatically calculate based on the information in the "Main" sheet.

Essentially, A full audit needs to be completed in every ten checks. So in the "Main Spreadsheet" you will see "Full Audit" followed by a number of "sense". In the example in ROW 4 under Craig you can see that a full audit was carried out on case 1, and then 9 sense checks after that full audit. After the 9th sense check another full audit is then required (case 11) and then another 9 senses can be carried out. This process continues and continues.

What I would like the "Senses Available" sheet to do is to automatically calculate how may senses are available until a "Full Audit" is required. A full audit is always required after 9 senses . In the same example under Craig, you can see that 9 senses are available.

So I suppose I need something to look at each row, find the last Full Audit in that row, count how many "sense" are after the last "full audit" and then subtract that number of "sense" away from 9, and then show automatically in the ""Senses Available" sheet.

Sorry for any confusion, I hope this makes a bit more sense?