Results 1 to 7 of 7

Thread: Auto Calculate Based on cell meeting a condition

  1. #1

    Auto Calculate Based on cell meeting a condition



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #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. #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. #4
    Try this
    Attached Files Attached Files

  5. #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. #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. #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
  •