Index match over multiple sheets in excel 2013

问题:

This is my first post, if anything is not in order pleas let me know so I can correct it.

For a project, I'm building a prototype that reads pipeline component files into excel, extracts the parts and coordinates and displays them in a graph. With a macro that gives me the ability to order the parts by clicking in the graph on the coordinates, I sort the parts manually. In this sheet I need to add data like part name and the absolute Z value. Here is where my problem comes in.

So far I've been able to use a separate INDEX and MATCH function to call my missing information from one of the pipeline datasheets, but when I combine them, the return an error (I've had #N/A; #REF and #VALUE for starters).

My questions are;

1. how can I build the INDEX MATCH function to return the name and Z value from one sheet?

2. Is it possible to nest this function to search all sheets in the file to return these values?

3. I use a combination of formulas and visual basics, which one can be used to be more dynamic?

Any help is appreciated :)

Background info: I have at least 4 sheets with pipeline data, 1 sheet with the graph to click on and 1 sheet where the filtered data is placed.

The data from the the graph sheet is called from the datasheets. Then the data in the graph is selected and copied to Sheet3 where the black line is the recall from path, red is data from the graph sheet and blue one source for the grpah (I've used one datafile to start with) (purple/pink, things I've tried). Then the required data has to be called from the datasheet, all the datasheets have the same format.

Images sheet 3

datasheet

I did find some information about a VLOOKUP across multiple sheets], but I was unable to implement the example in my situation. I've also checked the subReddits Excel and visual basic for information with little success.

回答1:

Ideally, your data should all be consolidated in one tab. If you're ever trying to execute a lookup across multiple tabs, it probably means you need to structure your data source differently. Whatever criteria that differentiates the different tabs, just make that a new column on the table.

In case you're still wondering about #1, you can just concatenate multiple Index/Match functions together and change the column #.

=INDEX($B$2:$D$8,MATCH($G$3,$A$2:$A$8,0),1)&" - "&INDEX($B$2:$D$8,MATCH($G$3,$A$2:$A$8,0),2)

回答2:

With the help of u/semicolonsemicolon from the website Reddit. here is the VBA solution to my own problem. For this code to work check my workbook that contains the modules that I've used. For my solution I've used some of john peltiers code on "how to get x andy on your chart". The code in the class module can be swapped for the code below. Thank you for taking the time to check my problem.

Private Sub EmbChart_MouseUp _
    (ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Double, myY As Double
Dim rng As Range, t As String

If Button = xlPrimaryButton Then
    With EmbChart
       Call .GetChartElement(X, Y, ElementID, Arg1, Arg2) ' call the coordinates that have been clicked

        Application.StatusBar = "[" & ElementID & "]"

         If ElementID = xlSeries Or ElementID = xlDataLabel Then
                If Arg2 > 0 Then
                    myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
                    myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
                    Application.StatusBar = "[" & myX & ", " & myY & "]"
                    Select Case Arg1
                     Case 1: t = "tabbleud"
                     Case 3: t = "tabblad"
                     Case 4: t = "tableeed"
                    End Select
                    With ActiveWorkbook.Worksheets("Sheet3").[B200].End(xlUp).Offset(1, 0)
                          .Value = myX
                          .Offset(0, 1).Value = myY
                          .Offset(0, -1).Value = Evaluate("=INDEX(" & t & "[IDENT name],SUMPRODUCT((" & t & "[X]=" & myX & ")*(" & t & "[Y]=" & myY & ")*(ROW(" & t & "[X])-ROW($A$2)+1)))")
                          .Offset(0, 2).Value = Evaluate("=INDEX(" & t & "[Z],SUMPRODUCT((" & t & "[X]=" & myX & ")*(" & t & "[Y]=" & myY & ")*(ROW(" & t & "[X])-ROW($A$2)+1)))")
                        End With
                End If
        Else
            If TypeName(Selection) = "Nothing" Then
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            ElseIf ElementID = xlShape Then

                'Chart Embedded in Chart Sheet
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            Else
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            End If
        End If

    End With
End If
Application.StatusBar = False
End Sub