Excel Archives - Page 2 of 4

Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula

October 30th, 2015July 6th, 2013 by John Dalesandro

The VLOOKUP function in Excel is very useful until there is a need to identify data satisfying multiple criteria. In the following example, I’ll demonstrate two different methods to perform a lookup against data that must meet multiple conditions. One method uses the SUMPRODUCT function and the other uses an array formula with MATCH and INDEX functions.

This example assumes that the data is structured in a way that will provide a unique result from the lookup. The standard use of the SUMPRODUCT and array formulas have a very different intent so if the result is not unique, you may see unexpected results.

In the sample worksheet below, there is a set of three periods specified. Column A contains the period number which is a simple sequential number representing a unique ID for the period. Column B and Column C define start and end dates for each period. There are no overlapping dates between each of the defined periods. Column E contains a small list of individual dates. I want to automatically identify which period the date in Column E belongs to in Column F. In order to belong to a period, the specific date in Column E should be greater than or equal to the period start date and less than or equal to the period end date.

Excel Multiple Criteria Lookup Sample Worksheet

Either formula should be entered starting in cell F2 (or modified to fit your particular needs).

Method 1 – Lookup using the SUMPRODUCT function

=SUMPRODUCT(--($B$2:$B$4<=E2),--($C$2:$C$4>=E2),$A$2:$A$4)

1	=SUMPRODUCT(--($B$2:$B$4<=E2),--($C$2:$C$4>=E2),$A$2:$A$4)

Method 2 – Lookup using an array formula with MATCH and INDEX functions

=INDEX($A$2:$A$4,MATCH(1,(($B$2:$B$4<=E2)*($C$2:$C$4>=E2)),0))

1	=INDEX($A$2:$A$4,MATCH(1,(($B$2:$B$4<=E2)*($C$2:$C$4>=E2)),0))

Since Method 2 uses an array formula, hit CTRL+SHIFT+ENTER after entering the formula in cell F2 otherwise Excel will show “#N/A” instead of the correct result.

Import Worksheets From One Workbook to Another Using VBA

October 20th, 2015June 15th, 2013 by John Dalesandro

The procedure ImportWorksheetsFromClosedWorkbook() may be used to copy all worksheets from one workbook (source) into a different workbook (target). The procedure also removes all references to external workbooks. This procedure may be useful if the target workbook has a series of predefined charts or pivot tables that rely on data contained in an external workbook published by another entity.

Option Explicit

Sub ImportWorksheetsFromClosedWorkbook()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim strFilename As String
    Dim strOriginalActiveWsName As String
    Dim bIsFileOpen As Boolean
    Dim objDefinedName As Object
    Dim bEnableEvents As Boolean
    Dim bDisplayAlerts As Boolean
    Dim bScreenUpdating As Boolean
    Dim i As Long
    
    bEnableEvents = Application.EnableEvents
    bDisplayAlerts = Application.DisplayAlerts
    bScreenUpdating = Application.ScreenUpdating
    
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbTarget = ActiveWorkbook
    strOriginalActiveWsName = wbTarget.ActiveSheet.Name

    strFilename = "c:\test.xlsx"
    
    If Not IsFileExist(strFilename) Then
        MsgBox "File not found: " & strFilename
        Exit Sub
    End If
    
    If IsWbOpen(strFilename) Then
        Set wbSource = Workbooks(strFilename)
        bIsFileOpen = True
    Else
        Set wbSource = Workbooks.Open(strFilename)
        bIsFileOpen = False
    End If

    For i = 1 To wbSource.Sheets.Count
        On Error Resume Next
        If Not wbTarget.Sheets(wbSource.Sheets(i).Name) Is Nothing Then
            wbTarget.Sheets(wbSource.Sheets(i).Name).Delete
        End If
        On Error GoTo 0
    Next

    For Each objDefinedName In wbTarget.Names
        objDefinedName.Delete
    Next objDefinedName

    For i = 1 To wbSource.Sheets.Count
        wbSource.Sheets(i).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
    Next

    If Not bIsFileOpen Then wbSource.Close False

    Call BreakExternalReferences
    Call DeleteExternalNames

    wbTarget.Sheets(strOriginalActiveWsName).Activate

    MsgBox "Source file successfully copied to this workbook."
     
    Application.ScreenUpdating = bScreenUpdating
    Application.DisplayAlerts = bDisplayAlerts
    Application.EnableEvents = bEnableEvents

    Set wbTarget = Nothing
    Set wbSource = Nothing
End Sub

Function IsFileExist(wbName As String) As Boolean
    IsFileExist = Len(Dir(wbName))
End Function

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
    On Error GoTo 0
End Function

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub

100

101

102

103

104

105

Option Explicit

Sub ImportWorksheetsFromClosedWorkbook()

Dim wbSource As Workbook

Dim wbTarget As Workbook

Dim strFilename As String

Dim strOriginalActiveWsName As String

Dim bIsFileOpen As Boolean

Dim objDefinedName As Object

Dim bEnableEvents As Boolean

Dim bDisplayAlerts As Boolean

Dim bScreenUpdating As Boolean

Dim i As Long

bEnableEvents = Application.EnableEvents

bDisplayAlerts = Application.DisplayAlerts

bScreenUpdating = Application.ScreenUpdating

Application.EnableEvents = False

Application.DisplayAlerts = False

Application.ScreenUpdating = False

Set wbTarget = ActiveWorkbook

strOriginalActiveWsName = wbTarget.ActiveSheet.Name

strFilename = "c:\test.xlsx"

If Not IsFileExist(strFilename) Then

MsgBox "File not found: " & strFilename

Exit Sub

End If

If IsWbOpen(strFilename) Then

Set wbSource = Workbooks(strFilename)

bIsFileOpen = True

Else

Set wbSource = Workbooks.Open(strFilename)

bIsFileOpen = False

End If

For i = 1 To wbSource.Sheets.Count

On Error Resume Next

If Not wbTarget.Sheets(wbSource.Sheets(i).Name) Is Nothing Then

wbTarget.Sheets(wbSource.Sheets(i).Name).Delete

End If

On Error GoTo 0

For Each objDefinedName In wbTarget.Names

objDefinedName.Delete

Next objDefinedName

For i = 1 To wbSource.Sheets.Count

wbSource.Sheets(i).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)

If Not bIsFileOpen Then wbSource.Close False

Call BreakExternalReferences

Call DeleteExternalNames

wbTarget.Sheets(strOriginalActiveWsName).Activate

MsgBox "Source file successfully copied to this workbook."

Application.ScreenUpdating = bScreenUpdating

Application.DisplayAlerts = bDisplayAlerts

Application.EnableEvents = bEnableEvents

Set wbTarget = Nothing

Set wbSource = Nothing

End Sub

Function IsFileExist(wbName As String) As Boolean

IsFileExist = Len(Dir(wbName))

End Function

Function IsWbOpen(wbName As String) As Boolean

On Error Resume Next

IsWbOpen = Len(Workbooks(wbName).Name)

On Error GoTo 0

End Function

Sub BreakExternalReferences()

Dim arLinks As Variant

Dim i As Long

arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

If IsArray(arLinks) Then

For i = LBound(arLinks) To UBound(arLinks)

ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks

Next i

End If

End Sub

Sub DeleteExternalNames()

Dim objDefinedName As Object

For Each objDefinedName In ActiveWorkbook.Names

If InStr(objDefinedName.RefersTo, "[") > 0 Then

objDefinedName.Delete

End If

Next objDefinedName

End Sub

Excel VBA Procedures to Break All External References in Workbook

October 20th, 2015May 25th, 2013 by John Dalesandro

The following Excel VBA procedures may be used to break all external references and links in the active workbook. The BreakExternalReferences() procedure will change all formulas containing references to external workbooks into the resulting value of that formula. The DeleteExternalNames() procedure will delete all defined names that contain external workbook references. Again, any formulas containing references to the defined name will be converted to the resulting value of that formula.

Option Explicit

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub

Option Explicit

Sub BreakExternalReferences()

Dim arLinks As Variant

Dim i As Long

arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

If IsArray(arLinks) Then

For i = LBound(arLinks) To UBound(arLinks)

ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks

Next i

End If

End Sub

Sub DeleteExternalNames()

Dim objDefinedName As Object

For Each objDefinedName In ActiveWorkbook.Names

If InStr(objDefinedName.RefersTo, "[") > 0 Then

objDefinedName.Delete

End If

Next objDefinedName

End Sub