CODEWITHSUNDEEP
excelvba
Fred
Fred

Reputation: 9

Using Sumifs on a range in a separate spreadsheet

I am currently preforming a Sumifs calculation using the follow code

 Public Function WFPAID(rev_date As Date) As Variant

  Application.Volatile (True)

  Set Vstatus = Sheets("KRONOS").Range("$DL:$DL")
  Set Team = Sheets("KRONOS").Range("$DO:$DO")
  Set WF_Paydate = Sheets("KRONOS").Range("$DK:$DK")

            WFPAID = Application.WorksheetFunction.SumIfs( _
            Writer_Fee _
            , Team, "<>9" _
            , Vstatus, "<>rejected", Vstatus, "<>unverified" _
            , WF_Paydate, rev_date)

 End Function

The name of my Excel file is DATADUMP and is saved on our server in the following location U:\DATADUMP.xlsx. I would like to preform the Sumifs calculation in U:\DATADUMP.xlsx Sheet. KRONOS from a different Excel called NEWCAL.

Can one help please.

Upvotes: 1

Views: 1349

Answers (3)

Hari Seldon
Hari Seldon

Reputation: 1060

I wanted to post another method for doing this that I did not know was possible. According to Chip Pearson, you can:

call a UDF that is contained in another (open) workbook by using the workbook name in the formula. For example: ='MyBook.xls'!RectangleArea(A1,A2)

So this means that another method of calling your UDF would be to make sure the other WB is open: Workbooks.Open("U:\DATADUMP.xlsx", ReadOnly:=True)

Then in the other worksheet, make sure the function reads like this: ='DATADUMP.xlsx'!WFPAID([Cells reference to dat])

It seems a much simpler way of implementing your objective. You could accompany this with vba if you needed to, but you could also limit when the sheet calculates, or do a copy-pasteValues to preserve the results.

Upvotes: 0

Hari Seldon
Hari Seldon

Reputation: 1060

Paste this code into a module in NEWCALC.xlsx and run as a macro. It will output the result of the SumIfs calculation to a cell in NEWCALC. 

Sub CalculateSumIfsOnDATADUMP()

Dim wb As Workbook
Dim Vstatus As Range, Team As Range, WF_Paydate As Range, Writer_Fee As Range
Dim WFPAID As Variant
Dim rng As Range
Dim colCOUNT As Long

    '// turn off screen updating
    Application.ScreenUpdating = False

    '// open the source workbook as read only
    Set wb = Workbooks.Open("U:\DATADUMP.xlsx", ReadOnly:=True)

    ThisWorkbook.ActiveSheet.Activate

    For Each rng In Application.Selection
    rev_date = rng.Value

        With wb.Worksheets("KRONOS")

            '// Set range variables for SumIfs calculation
            '// (NOTE: 'Writer_Fee' range needs to be supplied)
            Set Writer_Fee = .Range("Something")
            Set Vstatus = .Range("$DL:$DL")
            Set Team = .Range("$DO:$DO")
            Set WF_Paydate = .Range("$DK:$DK")

            '// Do the SumIfs Calculation, and store in WFAID
            WFPAID = Application.WorksheetFunction.SumIfs(Writer_Fee, _
                                                          Team, "<>9", _
                                                          Vstatus, "<>rejected", _
                                                          Vstatus, "<>unverified", _
                                                          WF_Paydate, "=" & rev_date)
        End With

        '// Output the calculation result to a cell in the ActiveSheet
        ActiveSheet.Cells(rng.Row, rng.Column + Selection.Columns.Count) = WFPAID

    Next rng

    '// close the source workbook without saving any changes
    wb.Close False

    '// turn on screen updating
    Application.ScreenUpdating = True

End Sub

EDIT
I changed the code to read a selected range, and output the SumIfs result in the column next to it.

Upvotes: 1

Brian Camire
Brian Camire

Reputation: 4825

Assuming "DATADUMP.xlsx" is already open, you might try something like this:

Public Function WFPAID(rev_date As Date) As Variant
   Dim objWorksheet As Excel.Worksheet

    Dim objWriterFee As Excel.Range
    Dim objStatus As Excel.Range
    Dim objTeam As Excel.Range
    Dim objPaydate As Excel.Range

    Application.Volatile (True)

    ' This assumes "DATADUMP.xlsx" is already open.
    Set objWorksheet = Application.Workbooks("DATADUMP.xlsx").Worksheets("KRONOS")

    ' Change the next line to set the "writer fee" to a valid range.
    Set objWriterFee = objWorksheet.Range("?")
    Set objStatus = objWorksheet.Range("$DL:$DL")
    Set objTeam = objWorksheet.Range("$DO:$DO")
    Set objPaydate = objWorksheet.Range("$DK:$DK")

    WFPAID = _
        Application.WorksheetFunction.SumIfs _
        ( _
             objWriterFee, _
             objTeam, "<>9", _
             objStatus, "<>rejected", objStatus, "<>unverified", _
             objPaydate, rev_date _
        )
End Function

However, if "DATADUMP.xlsx" is not already open, you can't open it in the current application during recalculation.

Upvotes: 0

Related Questions