FILTER function across multiple sheets, display in separate sheet

Copper Contributor

I have 3 spreadsheets labeled 'lesson 1', 'lesson 2', and 'lesson 3'. I made a 4th spreadsheet labeled 'planner sheet'.

In the first cell in the 'planner sheet' I put the formula:

={FILTER('lesson 1'!B:Z, 'lesson 1'!A:A = WEEKNUM(TODAY())); FILTER('lesson 2'!B:Z, 'lesson 2'!A:A = WEEKNUM(TODAY())); FILTER('lesson 3'!B:Z, 'lesson 3'!A:A = WEEKNUM(TODAY()))}

 

What this is supposed to do is grab all the information in the lesson sheets cells B through Z based on the A column that has a numerical value of the week number and put them into the 'planner sheet'.

What I end up getting is just a red box in the 'planner sheet' after entering the formula.
Any help is much appreciated. Thank you!

3 Replies
Attach a Excel file to your post so that we can see your sample data correct your formula. You need VSTACK() function if you have access to beta channel or current preview channel.

@jenjers If you have access to VSTACK() function then could try.

 

=VSTACK(FILTER('Lesson 1'!B:Z,'Lesson 1'!A:A=WEEKNUM(TODAY())),FILTER('Lesson 2'!B:Z,'Lesson 2'!A:A=WEEKNUM(TODAY())),FILTER('Lesson 3'!B:Z,'Lesson 3'!A:A=WEEKNUM(TODAY())))

See the attached file.

 

@jenjers 

Sub CurrentWeekData()

    Dim WBK As Workbook
    Dim WKS As Worksheet
    Dim i As Long
    Dim wn As Long
    Dim j As Long
    Dim k As Long
    Dim m As Integer
    
    Worksheets("planner sheet").Range("A2:Z1048576").Clear
    
    k = 2
    
    wn = Format(Date, "ww")
        
    For Each WKS In ThisWorkbook.Worksheets
        If WKS.Name <> "planner sheet" Then
        
        i = WKS.Range("A" & Rows.Count).End(xlUp).Row
        
        For j = 2 To i
        If WKS.Cells(j, 1).Value = wn Then
        
        For m = 1 To 26
        Worksheets("planner sheet").Cells(k, m).Value = WKS.Cells(j, m).Value
        
        Next m
                
        k = k + 1
        Else
        End If
        Next j
                
        End If
    Next WKS

End Sub

Maybe with these lines of code. In the attached file you can click the button in cell AB2 to run the macro.