Summarize Multiple Tables Into One With Filter

[wryan_garner4]

Good afternoon all,
I am stuck.
I have multiple sheets, each with a table, all formatted identical. Sheets are labeled January, February, March, April, ....... The tables on the sheets are labeled Table_1 to Table_12 respectively. I have a thirteenth sheet for summary. I would like to be able to pull table data from each sheet, and apply it to the summary tab, dependent on a drop down. Cell B2 on the summary tab has a drop down of all Locations. When I select Mexico, for example, I would like to pull the data from all twelve tables that have Mexico in the Location column. Right now, I am using =FILTER(Table_1,Table1[Location]=$B$2,""), but this only pulls the information from Table 1, and not all tables. How do I adjust this formula to pull from all tables?
This is an example of one of my data sheets.

Test.xlsx
	A	B	C	D	E
1	Location	Person	Sales	Growth	Potential
2	United States	Mark Adams	$52,123.00	10%	$75,000.00
3	Mexico	Mark Adams	$12,532.00	3%	$15,000.00
4	Canada	Sally Jones	$42,356.00	8%	$16,500.00
January

Cells with Data Validation
Cell	Allow	Criteria
A2:A4	List	=Location
B2:B4	List	=Person

This is an example of my summary sheet.

Test.xlsx
	A	B	C	D	E
1
2	Location	Canada
3
4	Location	Person	Sales	Growth	Potential
5	Canada	Sally Jones	$42,356.00	8%	$16,500.00
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Summary

Cell Formulas
Range	Formula
A5:E5	A5	=FILTER(Table_1,Table_1[Location]=$B$2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2:D2	List	=Location

 

[Fluff]

How about like

=LET(ra,ROWS(Table_1),rb,ROWS(Table_2)+ra,rc,ROWS(Table_3)+rb,s,SEQUENCE(rc+ROWS(Table_4)),sc,SEQUENCE(,COLUMNS(Table_1)),data,IF(s<=ra,INDEX(Table_1,s,sc),IF(s<=rb,INDEX(Table_2,s-ra,sc),IF(s<=rc,INDEX(Table_3,s-rb,sc),INDEX(Table_4,s-rc,sc)))),FILTER(data,INDEX(data,,1)=B2))

 

[wryan_garner4]

How about like

=LET(ra,ROWS(Table_1),rb,ROWS(Table_2)+ra,rc,ROWS(Table_3)+rb,s,SEQUENCE(rc+ROWS(Table_4)),sc,SEQUENCE(,COLUMNS(Table_1)),data,IF(s<=ra,INDEX(Table_1,s,sc),IF(s<=rb,INDEX(Table_2,s-ra,sc),IF(s<=rc,INDEX(Table_3,s-rb,sc),INDEX(Table_4,s-rc,sc)))),FILTER(data,INDEX(data,,1)=B2))

This is exactly what i was after! Thank you.
Now that this is working for me, I am seeing that, in the event i have a blank cell in a column to the right of the Location column, this formula results in a '0' in the filtered field. Is there a way to prevent the filter from inserting a 0, and keeping it as a blank? I attempted to use ISBLANK() with an IF() function before the FILTER(), but that doesn't seem to make any difference to me.

 

[Fluff]

Will you have a 0 that needs to remain?

 

[wryan_garner4]

Yes. There is a possibility of having a 0 that needs to remain.

 

[Fluff]

Ok, how about

=LET(ra,ROWS(Table_1),rb,ROWS(Table_2)+ra,rc,ROWS(Table_3)+rb,s,SEQUENCE(rc+ROWS(Table_4)),sc,SEQUENCE(,COLUMNS(Table_1)),data,IF(s<=ra,INDEX(IF(Table_1="","",Table_1),s,sc),IF(s<=rb,INDEX(IF(Table_2="","",Table_2),s-ra,sc),IF(s<=rc,INDEX(IF(Table_3="","",Table_3),s-rb,sc),INDEX(IF(Table_4="","",Table_4),s-rc,sc)))),FILTER(data,INDEX(data,,1)=B2))

 

[wryan_garner4]

This worked beautifully! Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.