Vlookup across mutliple sheets

[Hoss97]

I am trying to shorten the formula in "F6". I still need add to this formula but it is already too long. Any help would be fantastic. Thanks<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[jbeaucaire]

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:

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)

 

[Hoss97]

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.

 

[jbeaucaire]

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.

 

[Hoss97]

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