Extract data from multiple tabs into summary tab

[JoshLyman]

I am looking to extract data from 3 tables which are kept on separate sheets (Sheet1, Sheet2, and Sheet3) into a summary table. I want to pull through the 'Ref' and the 'Comments' from the individual tables, only if 'Comments' is populated. Is this possible without VBA? The tables, including the summary table on Sheet4, are below:

Book2
	A	B	C
1	Ref	Date	Comments
2	B826202	23-Apr-72	Yes
3	B826325	03-Jan-88
4	B830594	25-Mar-84
5	B830684	16-Oct-72
6	B830861	29-Apr-87	Yes
7	B830870	24-Aug-79	Yes
Sheet1

Book2
	A	B	C
1	Ref	Date	Comments
2	B930742	11-May-89
3	B930763	19-Jul-74	Yes
4	B930868	07-Apr-82
5	B930946	21-Aug-83
6	B931036	21-Jul-77	Yes
7	B931186	27-Jun-74	Yes
8	B931213	05-Oct-74
Sheet2

Book2
	A	B	C
1	Ref	Date	Comments
2	F233095	28-Jun-77	Yes
3	F233356	30-May-76
4	F233377	03-Oct-83
5	F234325	09-Mar-69
6	F234349	08-Mar-75	Yes
7	F234601	16-Oct-80
Sheet3

Book2
	A	B
1	Ref	Comments
2
Sheet4

 

[Fluff]

For 365, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Ref	Date	Comments
2	B826202	23/04/1972	Yes				Ref	Date	Comments
3	B826325	03/01/1988					B826202	23/04/1972	Yes
4	B830594	25/03/1984					B830861	29/04/1987	Yes
5	B830684	16/10/1972					B830870	24/08/1979	Yes
6	B830861	29/04/1987	Yes				B930763	19/07/1974	Yes
7	B830870	24/08/1979	Yes				B931036	21/07/1977	Yes
8							B931186	27/06/1974	Yes
9							F233095	28/06/1977	Yes
10							F234349	08/03/1975	Yes
11
12
13	Ref	Date	Comments
14	B930742	11/05/1989
15	B930763	19/07/1974	Yes
16	B930868	07/04/1982
17	B930946	21/08/1983
18	B931036	21/07/1977	Yes
19	B931186	27/06/1974	Yes
20	B931213	05/10/1974
21
22
23
24	Ref	Date	Comments
25	F233095	28/06/1977	Yes
26	F233356	30/05/1976
27	F233377	03/10/1983
28	F234325	09/03/1969
29	F234349	08/03/1975	Yes
30	F234601	16/10/1980
31
Dashboard

Cell Formulas
Range	Formula
G2:I10	G2	=LET(v,VSTACK(Table2[#Headers],Table2,Table3,Table4),FILTER(v,INDEX(v,,3)<>""))
Dynamic array formulas.

But do not put this in a table.

 

[JoshLyman]

Thanks Fluff. Does this not work in a table?

I tried to convert it to a table before/after the fact but it says "Formulas or rich data types in the header row will be removed and converted to static text. Do you want to continue?"

This is not necessarily a deal breaker, but from a formatting perspective it would be preferred. Especially as I may want to have additional drop-down columns on the Summary sheet in future that would only be populated after the data was imported.

 

[Fluff]

Does this not work in a table?

No it doesn't. Tables do not support spill ranges.
That formula will auto-expand as comments are added/removed which will never happen in a table.

 

[JoshLyman]

Ok! Your solution works regardless

 

[Fluff]

You're welcome & thanks for the feedback.