Display concatenated source worksheet names in Query?

[itm]

I have a query which consolidates data from a few different worksheets. Broadly, the data consists of stock holdings - stock names/codes and current values. A specific stock may appear in one or more worksheets.
I'd like one of the columns in the query to show which worksheet(s) data was derived from. For example, if a particular stock is found in Sheet1 and Sheet4, then the required column would contain "Sheet1,Sheet4".
Is this possible?

 

[alansidman]

Simple answer is Yes. How to do it will depend on seeing some sample data to develop schema

 

[itm]

OK that sounds promising. Here's some sample data..
The first 3 worksheets contain the base data. The sheets don't all have the same layouts, but the "Description" and "Valuation" columns are common, and are used as the basis of the aggregation. One of the stocks appears in more than one sheet (BLACKROCK ENERGY & RES INC TST PLC ORD GBP0.01). The 4th sheet contains the summary/aggregate data from the other 3 sheets. I'd like the "In Worksheets" column to show which worksheets the data was derived from. So for example cell H3 in the Summary worksheet would contain "II SIPP,II ISA".
This is the first time I've tried to upload sample data, so I hope I've interpreted the instructions correctly!

inv.xlsm
	A	B	C	D	E	F	G
1	Symbol	Qty	Description	Price	Change	Chg %	Valuation
2	BBGI	3079	BBGI GLOBAL INFRASTRUCTURE S.A. ORD NPV (DI)	162.40p	1.20p	0.74%	£5,000.30
3	BERI	1234	BLACKROCK ENERGY & RES INC TST PLC ORD GBP0.01	106.50p	-2.87p	-2.63%	£1,314.21
4	DAC	165	DANAOS CORP COM USD0.01(POST REV SPLT)	£51.93	-£0.73	-1.39%	£8,567.62
II SIPP

inv.xlsm
	A	B	C	D	E	F
1	Description	Qty	Avg Cost	Book Cost	Latest Price	Valuation
2	3I INFRASTRUCTURE ORD NPV	1717	275.0542p	£4,722.68	331.50p	£5,691.86
3	AEW UK REIT PLC ORD GBP0.01	12500	79.4665p	£9,933.31	116.20p	£14,525.00
4	BBGI GBL INFRSTR S ORD NPV DI	7046	170.2908p	£11,998.69	162.60p	£11,456.80
5	BLACKROCK ENGY R ORD GBP0.01	7001	89.25p	£6,248.39	111.00p	£7,771.11
iWeb ISA

inv.xlsm
	A	B	C	D	E	F	G
1	Symbol	Qty	Description	Price	Change	Chg %	Valuation
2	APAX	2482	APAX GLOBAL ALPHA LTD ORD NPV	178.80p	0.60p	0.34%	£ 4,437.82
3	COIN	11	COINBASE GLOBAL INC COM USD0.00001 CL A	£40.94	-£1.18	-£0.03	£450.33
4	DGI9	19223	DIGITAL 9 INFRASTRUCTURE PLC ORD NPV	110.20p	-0.60p	-£0.01	£ 21,183.75
5	LWDB	604	LAW DEBENTURE CORP ORD GBP0.05	760.00p	-14.00p	-£0.02	£ 4,590.40
6	BERI	600	BLACKROCK ENERGY & RES INC TST PLC ORD GBP0.01	106.50p	-2.87p	-2.63%	£655.99
II ISA

inv.xlsm
	A	B	C	D	E	F	G	H
1	Symbol	Qty	Description	Price	Change	Chg %	Valuation	In Worksheets
2	BBGI	3079	BBGI GLOBAL INFRASTRUCTURE S.A. ORD NPV (DI)	162.40p	1.20p	0.74%	£5,000.30
3	BERI	1234	BLACKROCK ENERGY & RES INC TST PLC ORD GBP0.01	106.50p	-2.87p	-2.63%	£1,314.21
4	DAC	165	DANAOS CORP COM USD0.01(POST REV SPLT)	£51.93	-£0.73	-1.39%	£8,567.62
5		1717	3I INFRASTRUCTURE ORD NPV	275.0542p			£4,722.68
6		12500	AEW UK REIT PLC ORD GBP0.01	79.4665p			£9,933.31
7		7046	BBGI GBL INFRSTR S ORD NPV DI	170.2908p			£11,998.69
8		7001	BLACKROCK ENGY R ORD GBP0.01	89.25p			£1,970.20
9	APAX	2482	APAX GLOBAL ALPHA LTD ORD NPV	178.80p	0.60p	0.34%	£ 4,437.82
10	COIN	11	COINBASE GLOBAL INC COM USD0.00001 CL A	£40.94	-£1.18	-£0.03	£450.33
11	DGI9	19223	DIGITAL 9 INFRASTRUCTURE PLC ORD NPV	110.20p	-0.60p	-£0.01	£21,183.75
12	LWDB	604	LAW DEBENTURE CORP ORD GBP0.05	760.00p	-14.00p	-£0.02	£ 4,590.40
Summary

Cell Formulas
Range	Formula
G8	G8	='II SIPP'!G3+'II ISA'!G6