Vlookup across mutliple sheets

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You can try adding a USER-DEFINED FUNCTION (UDF) called Sumif3D2 to your sheet. It's a little weird in its syntax, but it's way shorter than what you're doing now.

put all this code in a standard module and save your sheet:
Code:
Function SumIf3D2(Range3D As String, Criteria As String, _
    Optional Sum_Range As String) As Variant
 
    Dim Sum As Double
    Dim vaRng1 As Variant, vaRng2 As Variant
    Dim i As Long
   
    Application.Volatile
   
    If Len(Sum_Range) = 0 Then
      Sum_Range = Range3D
    End If
 
    vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D)
    vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, Sum_Range)
   
    Sum = 0
    For i = LBound(vaRng1) To UBound(vaRng1)
        Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria, vaRng2(i))
    Next i
   
    SumIf3D2 = Sum
   
End Function
Function Parse3DRange2(wb As Workbook, _
                        SheetsAndRange As String) As Variant
 
    Dim sTemp As String
    Dim i As Long, j As Long
    Dim Sheet1 As String, Sheet2 As String
    Dim aRange() As Range
    Dim sRange As String
    Dim lFirstSht As Long, lLastSht As Long
    Dim rCell As Range
    Dim rTemp As Range
   
    On Error GoTo Parse3DRangeError
 
    sTemp = SheetsAndRange
       
    'if it's 3d, rtemp will be nothing
    On Error Resume Next
        Set rTemp = Range(sTemp)
    On Error GoTo Parse3DRangeError
   
    'if it's 3d, parse it
    If rTemp Is Nothing Then
       i = InStr(sTemp, "!")
       If i = 0 Then Err.Raise 9999
   
       'next line will generate an error if range is invalid
       'if it's OK, it will be converted to absolute form
       sRange = Range(Mid$(sTemp, i + 1)).Address
   
       sTemp = Left$(sTemp, i - 1)
       i = InStr(sTemp, ":")
       Sheet2 = Trim(Mid$(sTemp, i + 1))
       If i > 0 Then
           Sheet1 = Trim(Left$(sTemp, i - 1))
       Else
           Sheet1 = Sheet2
       End If
   
       'next lines will generate errors if sheet names are invalid
       With wb
           lFirstSht = .Worksheets(Sheet1).Index
           lLastSht = .Worksheets(Sheet2).Index
       
           'swap if out of order
           If lFirstSht > lLastSht Then
               i = lFirstSht
               lFirstSht = lLastSht
               lLastSht = i
           End If
             
           'load each cell into an array
           j = 0
           For i = lFirstSht To lLastSht
               For Each rCell In .Sheets(i).Range(sRange)
                   ReDim Preserve aRange(0 To j)
                   Set aRange(j) = rCell
                   j = j + 1
               Next rCell
           Next i
       End With
       
       Parse3DRange2 = aRange
    Else
        'range isn't 3d, so just load each cell into array
        For Each rCell In rTemp.Cells
            ReDim Preserve aRange(0 To j)
            Set aRange(j) = rCell
            j = j + 1
        Next rCell
       
        Parse3DRange2 = aRange
    End If
   
Parse3DRangeError:
    On Error GoTo 0
    Exit Function
 
End Function  'Parse3DRange

Now to use it, the syntax is thus:

=sumif3d2("'Square Trash Receptacle:FanBack Loveseat Glider'!$BD$4:$BD$26","H1","'Square Trash Receptacle:FanBack Loveseat Glider'!$BL$4:$BL$26")

Notice, all three parameters are strings, enclosed in quotes.

This requires your sheets to be next to each other in order, and the reference:

'Square Trash Receptacle:FanBack Loveseat Glider'

...represents the first sheet and the last in the sequence or sheets to match.

http://www.mrexcel.com/forum/showthread.php?t=368177

(source)
 
Upvote 0
I think I can make this work, but it may require a lot of modifications to my individual worksheets. Which is OK with me, unless we can modify the code to help.

=sumif3d2("'Square Trash Receptacle:FanBack Loveseat Glider'!$BD$4:$BD$26","H1","'Square Trash Receptacle:FanBack Loveseat Glider'!$BL$4:$BL$26")

Above is the code you gave me. I changed it to the following and was able to get it to return the value of "2" which was the correct value from the FanBack Loveseat Glider sheet.

=sumif3d2("6' Tables:FanBack Loveseat Glider!$BD$4:$BD$26",E1,"6' Tables:FanBack Loveseat Glider!$Bj$4:$Bj$26")

The problem is that the values I need added together from the different sheets are not always in the same columns. For example, in the Hex Table worksheet, the values are in "BM", another sheet has them in "BI". They could be anywhere in the range of "BD4:BM26".

Is there a way to change this so that it recognizes the variations?

Thanks for your help, and I can make the solution work by modifying the individual worksheets. Just wondering if there is a faster way.
 
Upvote 0
Since any model like yours benefits from consistency, then I'm going to stick with the solution offered and recommend you do the corrections in your data to make it all compatible and consistent so solutions like this will always work.
 
Upvote 0
Since any model like yours benefits from consistency, then I'm going to stick with the solution offered and recommend you do the corrections in your data to make it all compatible and consistent so solutions like this will always work.


This solution worked very well, and Thank you for your help. The sheet works perfectly and your solution saved time. Thanks again
 
Upvote 0

Forum statistics

Threads
1,196,391
Messages
6,015,006
Members
441,865
Latest member
Enragedpanda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top