# Thread: Auto Calculate Based on cell meeting a condition

1. ## Auto Calculate Based on cell meeting a condition

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.

Craig

2. Try this worksheet event cde

Code:
```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```

3. 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

4. Try this

5. 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,

6. 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.

7. 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?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•