Posts by pangolin

    Re: Multiple Critera search

    Do you really expect someone to help you when all you say is " I can not get the formula to work" :)

    COUNTIFS will "work" as per the description given by you in your earlier post....if there are more complexities then you may kindly provide more explanation or even upload a dummy file so that you get a more calibrated response

    Re: Finding the average based a predefined criteria

    this formula works for me

    =AVERAGEIFS('Report Format'!H5:H23,Ages,">="&C10,Ages,"<="&D10)

    it gives value of 13.8 under pre-test

    pl note that it seems there is some circluar in ur file and many times formulas return null values untile the Iterative Cal checkbox is ticked

    hope that helps

    Re: VBA: Input min value and return row values that meets min value (multiple sheets)

    see this code

    note the following

    the code takes an Output sheet for the output
    Sheet1 takes the column count

    change these references as reqd

    Re: Finding a result based on a matrix

    this is one approach


    note the three criteria are being inputted in cells B18 (utilisation) B19>> the % and B20 >> the decimal value

    this formula assumes that FL/DR/X/Y are the same across all the four matrices....and hence it only finds the correct matrix but within that matrix it does not specifically search for the next two values but takes standardise values to search within that matrix

    hope that helps

    Re: List names that match criteria

    this is one approach


    note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

    Re: Insurance Premium Payment Reminder for Agent

    you can use this formula to generate the next due date for your policy as compared to Todays date


    note here Col F takes the Commencement Date and a new Column M has to be provided setting out the number of payments each year for a policy

    see the attached file for more details

    you can then use Conidtional Formatting to highlight payments for next months or so

    Re: Open userform with values filled in based on selected row

    from what I have understood you want the userform to populate with the record which you have selected

    for that you can use something like this

    Private Sub UserForm_Initialize()
    txtLastName.Value = ActiveCell.EntireRow.Cells(1, 3).Value
    End Sub

    this will populate the text box for 'last value'with the last name of the row which you have selected

    you can write similar code for the other text boxes

    hope that helps

    Re: Creating Marco and Deleting Rows

    you can for eg add a command button and put this code within the "Click" action

    Private Sub CommandButton1_Click()
    'put the code here
    End Sub

    however note this code references the activesheet and will run only on the sheet where the command button is present

    to make the code run for other sheets you will have to apprpriately set the sheet references..

    Re: sum difference

    but ur Cell G38 is a product of G30 (and following three cells) which is a sum of G19 and G20...hence putting formula in cell G20 will create a nasty circular and actually generate false values...Stephen's solution seems to be quite appropriate

    Re: Creating Marco and Deleting Rows

    see if this code helps

    Sub copypsaste()
    Set myrange = ActiveSheet.UsedRange
    Set nbook = Workbooks.Add
    myrange.Copy Destination:=nbook.Sheets("sheet1").Range("A1")
    For Each cell In Sheets("sheet1").UsedRange
    If cell.Value = "empty" Then cell.EntireRow.Delete
    Next cell
    End Sub