EXCEL - Query list and return values

Question

I have a database with the following information:

I need to represent this information in graphic, and the need arose to create a query to search the values according to the selected week and month.

For this, I used the formula INDEX in the Plan/Project column to return the plan name (Ex: "NA1_0835_021_REV1_9") and then used PROCV to fetch the values corresponding to each plan/project.

My problem: How to search ALL the Plans for Week 1 of 2018, but I don’t know how to indicate where the LINES start and end within the matrix selected to compose the formula INDEX

=INDICE(MATRIZ;NUMERO_LINHA;NUMERO_COLUNA

Another difficulty is to automate the query according to the YEAR and WEEK, where I can link these filters within the query?

Answer 1

In these circumstances, exactly the way your spreadsheet is, I would leave the formulas aside and do the following:

Open the VBA editor, insert a new module and paste the following code:

Sub getProjetos()

Dim semana, ano, thisplan As String

ano = Range("C2")
semana = Range("C3")
thisplan = ActiveSheet.Name

    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Sheets("Planos").Select
    On Error Resume Next
    Sheet1.ShowAllData
    On Error GoTo -1
    ActiveSheet.Range("$A$5:$I$1000000").AutoFilter Field:=4, Criteria1:=semana
    ActiveSheet.Range("$A$5:$I$1000000").AutoFilter Field:=2, Criteria1:=ano
    Range("A100000").End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets(thisplan).Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

IMPORTANT: Run the code in the worksheet where you want the result, not in the Plans. You can create a button next to the Year and Week box and assign this macro as well. Try it on and tell me..