Announcement

Collapse
No announcement yet.

Multiple tasks within same column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excelnubie99
    started a topic Multiple tasks within same column

    Multiple tasks within same column

    I need a formula that will do the following;

    If Column A is greater than or equal to 1 million AND Column C says Workers Compensation insert 1 million in Column B. If Column A is less than 1 million, insert amount of column A.

    If Column A is greater than or equal to 3 million AND Column C says General Liability, insert 3 million in Column B. If Column A is less than 3 million, insert amount of column A.

    I am open to what ever you think is the best solution. Sample spreadsheet is attached.
    Thank you!


    Attached Files

  • NBVC
    replied
    Sorry for the delay.... was on vacation...

    try:
    Code:
    ws.Range("K2").FormulaR1C1 = "=IF(TRIM(RC[-8])=""WORKERS COMPENSATION"",MIN(RC[-10],1000000),IF(TRIM(RC[-8])=""GENERAL LIABILITY"",MIN(RC[-10],3000000),""""))"

    Leave a comment:


  • Excelnubie99
    replied
    This formula worked perfectly. Are you able to inset it into the following macro in the bolded line and remove the old formula? I don't know how to convert it to VBA.

    Sub InsertCol()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim LastRow As Long
    For Each ws In Sheets
    If ws.Name <> "Summary" Then
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws.Columns("K:K").Insert
    ws.Range("K1").FormulaR1C1 = "Incurred Total Within Deductible"
    ws.Range("K2").FormulaR1C1 = "=IF(RC[-1]<1000000,(RC[-1]),""1000000"")"
    ws.Range("K2").AutoFill Destination:=ws.Range("K2:K" & LastRow), Type:=xlFillDefault
    ws.Range("K" & LastRow + 1) = WorksheetFunction.Sum(ws.Range("K2:K" & LastRow))
    ws.Range("K" & LastRow + 2) = ws.Range("K" & LastRow + 1).Value * 0.1
    ws.Range("K" & LastRow + 3) = WorksheetFunction.Sum(ws.Range("K" & LastRow + 1) + ws.Range("K" & LastRow + 2))
    ws.Range("K" & LastRow + 1 & ":K" & LastRow + 3).NumberFormat = "#,##0.00"
    ws.Range("L" & LastRow + 1) = "Sub Total "
    ws.Range("L" & LastRow + 2) = "LCF @ 10% of Subtotal above"
    ws.Range("L" & LastRow + 3) = "Total Incurred Within Deductible"
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "A").End(xlUp).Offset(1, 0) = ws.Name
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "B").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 1)
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "C").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 2)
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "D").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 3)
    End If
    Next ws
    Application.ScreenUpdating = True
    End Sub

    Leave a comment:


  • NBVC
    replied
    Try:


    =IF(TRIM(C2)="WORKERS COMPENSATION",MIN(A2,1000000),IF(TRIM(C2)="GENERAL LIABILITY",MIN(A2,3000000),""))

    Leave a comment:


  • Excelnubie99
    replied
    Thank you, this is very close. Can you also add, show column A amount in Column B if it is below the cap?

    Leave a comment:


  • NBVC
    replied
    Doesn't seem to be the case in your sample data....

    anyway, give this a go:

    =IF(TRIM(C2)="WORKERS COMPENSATION",IF(A2>=1000000,1000000,""),IF(TRIM(C2)="GENERAL LIABILITY",IF(A2>=3000000,3000000,""),""))

    copied down.

    Leave a comment:


  • Excelnubie99
    replied
    If Column C is Workers Compensation then it will always have a 1 million cap. If Column C is General Liability it will always have a 3 million cap. If Column C has no data, then leave column B blank.

    Leave a comment:


  • NBVC
    replied
    What if column A is greater that 1 million or 3 million but column C doesn't match your criteria?

    Leave a comment:

Working...
X