Searching and Returning Results From One Sheet to Another

[c.clavin]

Hi guys, is there a way to use one sheet as a master database to pull from so I can output these results to another or multiple other sheets? I've tried VLOOKUP, INDEX, everything.

What I'm looking to do is have all of my categories in a specific section display the output of all of my results under certain headings. Is there any way to get this done with or without using macros? Almost need to pull from my master database and spit out the results on my display/print pages. I've been driving myself nuts trying to figure this out. Thanks!

Input

Output

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[c.clavin]

Hi Fluff, sorry about that, I'm using Office 365, version 16.53 on Mac. Thanks.

 

[Fluff]

Thanks for that, in which case you can use the Filter function, do you just want to return cols A, D,E & F?

 

[c.clavin]

That's what I'm currently doing. I made 2 tables for the 2 categories, so I can display 2 filters on the same sheet/page. I did not know if there was a better way but it does seem to be working this way, pretty well but not perfectly.

Is there a way to have my 'master' sheet automatically update the filters on my display pages when I make changes to it? This would make it work exactly like what I had in mind. Thanks again.

 

[Fluff]

I was referring to the Excel Filter function, but it sounds like you are talking about the Autofilter.
Something like

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	County	GridRef	District	Ward
2	Cumbria	NY777439	Eden	Alston Moor		Derbyshire
3	Derbyshire	SK055741	High Peak	Corbar		High Peak	Corbar
4	Derbyshire	SK073782	High Peak	Limestone Peak		High Peak	Limestone Peak
5	West Yorkshire	SE091296	Bradford	Queensbury		High Peak	Cote Heath
6	Derbyshire	SK063719	High Peak	Cote Heath		High Peak	Barms
7	Derbyshire	SK065738	High Peak	Barms		High Peak	Temple
8	West Yorkshire	SE116305	Bradford	Queensbury		High Peak	Corbar
9	Derbyshire	SK054727	High Peak	Temple		Derbyshire Dales	Tideswell
10	Staffordshire	SK084589	Staffordshire Moorlands	Manifold
11	Derbyshire	SK059739	High Peak	Corbar
12	Derbyshire	SK155760	Derbyshire Dales	Tideswell
13	Cheshire	SJ978767	Cheshire East	Sutton
14	West Yorkshire	SE071180	Calderdale	Greetland and Stainland
15	Greater Manchester	SD953077	Oldham	St James'
16	West Yorkshire	SE123307	Bradford	Queensbury
17	West Yorkshire	SE048170	Calderdale	Ryburn
18	West Yorkshire	SE071341	Bradford	Bingley Rural
19	West Yorkshire	SE069284	Calderdale	Illingworth and Mixenden
20	Staffordshire	SK056479	Staffordshire Moorlands	Churnet
Lists

Cell Formulas
Range	Formula
F3:G9	F3	=FILTER(C2:D20,A2:A20=F2)
Dynamic array formulas.

 

[c.clavin]

Great, thank you. Does this still work if I linked one workbook to another, or one sheet to another?

 

[Fluff]

Yes you can have the data & formula in different sheets or different workbooks.

 

[c.clavin]

Oh wow this is great thank you. Had no idea you could do this so easily with non-macros. I appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.