Combining FILTER, VSTACK, INDIRECT

[pholt33]

I have a file which currently contains 15 tabs with data that I want to filter and show on a summary tab. I have been able to accomplish this with an ugly formula...

It works but if there is a new tab added, then I have to manually update the formula when I would rather update the list of tab names. What I want to do is have a list of tab names, dynamically build the FILTER formulas, and then wrap a VSTACK around it. Sort of like VSTACK( B3, B4).

Here is how I created the individual FILTERs...

How can I do this?

 

[Fluff]

Do you need to have something showing a sheet does not have any values >5%, or is that just to stop the vstack from showing #CALC?

 

[Jeffrey Mahoney]

This may be an approach. The formula below can read a range of sheets and stack the values from all those sheets. I was thinking that you could create a formula just for stacking the values and reference that as a named range.
=VSTACK(Sheet1:Sheet2!B2:B13)

 

[pholt33]

Do you need to have something showing a sheet does not have any values >5%, or is that just to stop the vstack from showing #CALC?

I put the "none" part in to prevent #CALC and just to have a visual acknowledgement that the tab wasnt missed.

 

[Fluff]

In that case you can do it like Jeffrey has suggested, use Vstack to pull all the data in & then filter that.
For it to work all the sheets will need to be consecutive & if need you could create a start & end sheet which will be blank & then put all the sheets you want between them.

 

[HighAndWilder]

I have a file which currently contains 15 tabs with data that I want to filter and show on a summary tab. I have been able to accomplish this with an ugly formula...

View attachment 117987

It works but if there is a new tab added, then I have to manually update the formula when I would rather update the list of tab names. What I want to do is have a list of tab names, dynamically build the FILTER formulas, and then wrap a VSTACK around it. Sort of like VSTACK( B3, B4).

Here is how I created the individual FILTERs...

View attachment 117988

How can I do this?

This code will include in the filter ALL sheets apart from a sheet called Summary.

If there are other sheets that need to be excluded let me know.

It does not consider sheets with more than 1000 rows. Possible if needed.

It does not include a header row or a column to indicate where the data came from. Both of these are possible if needed.

Private Sub subCompileFilter()
Dim Ws As Worksheet
Dim strFormula As String

  ActiveWorkbook.Save
 
  With Worksheets("Summary")
  
    Cells.Clear
    
    For Each Ws In ActiveWorkbook.Worksheets
    
      If Ws.Name <> "Summary" Then

        strFormula = strFormula & ",FILTER('" & Ws.Name & "'!A3:S1000," & Ws.Name & "!R3:R1000>=5%,""" & Ws.Name & "-None" & """)"
        
      End If
      
    Next Ws
  
    .Range("A1").Formula2 = "=VSTACK(" & Mid(strFormula, 2) & ")"
  
  End With
  
End Sub

 

[pholt33]

This may be an approach. The formula below can read a range of sheets and stack the values from all those sheets. I was thinking that you could create a formula just for stacking the values and reference that as a named range.
=VSTACK(Sheet1:Sheet2!B2:B13)

In that case you can do it like Jeffrey has suggested, use Vstack to pull all the data in & then filter that.
For it to work all the sheets will need to be consecutive & if need you could create a start & end sheet which will be blank & then put all the sheets you want between them.

Ive used the Sheet1:Sheet10 method before but wasnt sure how it would work if other tabs were added or removed. Your Start and End idea helped clarify how that works.

Now that I have a staging tab with =VSTACK(START:END!$A$3:$S$1000), I created a dynamic named range called StagingData. I want to filter the results where the values in column R > 5%. I couldnt get it to work and Copilot told me =FILTER(StagingData, StagingData[Column18]>5%) That produces a #VALUE. How can I do this last bit of filtering on a named range?

 

[Fluff]

How about

=let(data,VSTACK(START:END!$A$3:$S$1000),filter(data,index(data,,18)>5%))

 

[Jeffrey Mahoney]

Try this
=FILTER(StagingData, INDEX(StagingData,,18)>5%)

 

[pholt33]

Try this
=FILTER(StagingData, INDEX(StagingData,,18)>5%)

Perfect!

Thanks guys, much appreciated!