Pulling data from a master table into another sheet

[Tatum2020]

Hi,

I hope somebody can help me. I have a master table with all my data and I am trying to pull data from the mater table into another sheet. I do not want to pull all the columns from the master table, but just a couple and the data should be pulled based on a filter on one of the columns. Is this possible? I have tried to google and I suspect that I need to use index match formula but still struggling. Please help, it is fairly urgent.

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Postcode	In use?	Latitude	Longitude	Easting	Northing	Grid Ref	County			Postcode	In use?	Latitude	Longitude	Easting	Northing
2	CA11 0SA	Tom	54.615912	-2.908883	341403	524908	NY414249	Cumbria			CA11 0SA	Tom	54.61591	-2.90888	341403	524908
3	DY14 0JB	Tom	52.409181	-2.544818	363036	1110	SO630791	Shropshire			SK6 7HW	Jerry	53.38499	-2.05845	396210	387573
4	EX22 7NB	Tom	50.849725	-4.33216	235926	108170	SS359081	Devon			ST1 2HT	Sue	53.02929	-2.17001	388695	348014
5	OL14 6XB	Tom	53.680058	-2.078324	394924	420402	SD949204	West Yorkshire			B31 5HY	Dave	52.40158	-1.99847	400201	278174
6	BD12 8DW	Tom	53.747261	-1.772511	415100	427900	SE151279	West Yorkshire
7	PL18 9HR	Tom	50.515922	-4.22854	242102	70831	SX421708	Cornwall
8	SK6 7HW	Jerry	53.384991	-2.058445	396210	387573	SJ962875	Greater Manchester
9	TN27 0HW	Jerry	51.226763	0.781863	594335	151185	TQ943511	Kent
10	BB1 8JR	Jerry	53.761038	-2.495776	367413	429523	SD674295	Lancashire
11	NE19 2LS	Jerry	55.089229	-2.012488	399301	577199	NY993771	Northumberland
12	BA4 4FG	Jerry	51.189244	-2.4763	366810	143443	ST668434	Somerset
13	CR3 9BS	Jerry	51.305874	-0.056082	535600	158100	TQ356581	Surrey
14	DH9 6TQ	Jerry	54.866323	-1.688048	420120	552438	NZ201524	County Durham
15	ST1 2HT	Sue	53.029293	-2.170013	388695	348014	SJ886480	Staffordshire
16	RG9 6YJ	Sue	51.593958	-0.914808	475269	188903	SU752889	Buckinghamshire
17	TN16 3DX	Sue	51.315114	0.033652	541826	159297	TQ418592	Greater London
18	BB8 0PG	Sue	53.857663	-2.157957	389708	440171	SD897401	Lancashire
19	DL6 3AQ	Sue	54.372545	-1.289145	446278	497681	SE462976	North Yorkshire
20	ST8 6JA	Sue	53.11329	-2.174573	388412	357359	SJ884573	Staffordshire
21	B31 5HY	Dave	52.401582	-1.998472	400201	278174	SP002781	West Midlands
22	HD8 8EB	Dave	53.585907	-1.699963	419960	409966	SE199099	West Yorkshire
23	PL32 9RH	Dave	50.587138	-4.701739	208847	79863	SX088798	Cornwall
24	HD7 6DB	Dave	53.600607	-1.92882	404808	411562	SE048115	West Yorkshire
25	BB12 9QJ	Dave	53.83691	-2.256667	383207	437881	SD832378	Lancashire
26
Main

Cell Formulas
Range	Formula
K2:K25	K2	=IFERROR(INDEX(A$2:A$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=L2)/(L2<>""),1)),"")
L2:L25	L2	=IFERROR(INDEX(B$2:B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100<>"")/(ISNA(MATCH($B$2:$B$100,L$1:L1,0))),1)),"")
M2:P25	M2	=IFERROR(INDEX(C$2:C$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$L2)/($L2<>""),1)),"")

 

[Tatum2020]

It s

=IFERROR(INDEX(B$2:B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100<>"")/(ISNA(MATCH($B$2:$B$100,L$1:L1,0))),1)),"")

This no longer works because it doesn't allow me to filter on "in use" whereas in the previous answer you provided I could filter on "Yes" or "No" (in my case it is Source). So I want to be able to select a source option & then it shows me one row per name for a one order number. So in B17 of the below, that would be the drop-down list. Depending on what I slelect in the drop down, the summary table would change, but only show one line per request no.

 

[Fluff]

Which column contains the value to filter on?

 

[Tatum2020]

Which column contains the value to filter on?

In the attached example its Column B (cell B17)

 

[Fluff]

But your master table does not have anything that says Sourcing.

 

[Tatum2020]

Apologies, I have corrected it now: So B17 would reference column F in my master table.

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Postcode	In use?	Latitude	Longitude	Easting	Northing	Grid Ref	County			Postcode	In use?	Latitude	Longitude	Easting	Northing
2	CA11 0SA	Tom	54.615912	-2.908883	341403	Source	NY414249	Cumbria		Store	EX22 7NB	Tom	50.84973	-4.33216	235926	store
3	DY14 0JB	Tom	52.409181	-2.544818	363036	Source	SO630791	Shropshire			TN27 0HW	Jerry	51.22676	0.781863	594335	Store
4	EX22 7NB	Tom	50.849725	-4.33216	235926	store	SS359081	Devon			ST1 2HT	Sue	53.02929	-2.17001	388695	Store
5	OL14 6XB	Tom	53.680058	-2.078324	394924	Source	SD949204	West Yorkshire			B31 5HY	Dave	52.40158	-1.99847	400201	Store
6	BD12 8DW	Tom	53.747261	-1.772511	415100	Source	SE151279	West Yorkshire
7	PL18 9HR	Tom	50.515922	-4.22854	242102	Source	SX421708	Cornwall
8	SK6 7HW	Jerry	53.384991	-2.058445	396210	Source	SJ962875	Greater Manchester
9	TN27 0HW	Jerry	51.226763	0.781863	594335	Store	TQ943511	Kent
10	BB1 8JR	Jerry	53.761038	-2.495776	367413	Store	SD674295	Lancashire
11	NE19 2LS	Jerry	55.089229	-2.012488	399301	Store	NY993771	Northumberland
12	BA4 4FG	Jerry	51.189244	-2.4763	366810	Store	ST668434	Somerset
13	CR3 9BS	Jerry	51.305874	-0.056082	535600	Store	TQ356581	Surrey
14	DH9 6TQ	Jerry	54.866323	-1.688048	420120	Store	NZ201524	County Durham
15	ST1 2HT	Sue	53.029293	-2.170013	388695	Store	SJ886480	Staffordshire
16	RG9 6YJ	Sue	51.593958	-0.914808	475269	Store	SU752889	Buckinghamshire
17	TN16 3DX	Sue	51.315114	0.033652	541826	Store	TQ418592	Greater London
18	BB8 0PG	Sue	53.857663	-2.157957	389708	Store	SD897401	Lancashire
19	DL6 3AQ	Sue	54.372545	-1.289145	446278	Store	SE462976	North Yorkshire
20	ST8 6JA	Sue	53.11329	-2.174573	388412	Store	SJ884573	Staffordshire
21	B31 5HY	Dave	52.401582	-1.998472	400201	Store	SP002781	West Midlands
22	HD8 8EB	Dave	53.585907	-1.699963	419960	Store	SE199099	West Yorkshire
23	PL32 9RH	Dave	50.587138	-4.701739	208847	Store	SX088798	Cornwall
24	HD7 6DB	Dave	53.600607	-1.92882	404808	Store	SE048115	West Yorkshire
25	BB12 9QJ	Dave	53.83691	-2.256667	383207	Store	SD832378	Lancashire
26
Main

Cell Formulas
Range	Formula
K2:K25	K2	=IFERROR(INDEX(A$2:A$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=L2)/($F$2:$F$100=$J$2),1)),"")
L2:L25	L2	=IFERROR(INDEX(B$2:B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($F$2:$F$100=$J$2)/(ISNA(MATCH($B$2:$B$100,L$1:L1,0))),1)),"")
M2:P25	M2	=IFERROR(INDEX(C$2:C$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$L2)/($F$2:$F$100=$J$2),1)),"")

 

[Tatum2020]

This seems to work. Thank you very much! I haven't managed to make it dynamic, still need to drag and drop the formula to other rows.

 

[Fluff]

As you don't have the Filter function, then you will need to drag the formula down.

 

[Tatum2020]

s you don't have the Filter function, then you

As you don't have the Filter function, then you will need to drag the formula down.

Hi Fluff, another question. So I updated my master table with a new row "postcode" but a different "in use?" name. However, it does not update my summary table. It only updates records with both different "postcode" and "in use?" Is this because it is indexing the "in use" and not the post code?