Pulling data from a master table into another sheet

T

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
37
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about something like
+Fluff v2.xlsm
ABCDEFGHIJKL
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyPostcodeGrid Ref
2CA11 0SAYes54.615912-2.908883341403524908NY414249CumbriaWest YorkshireOL14 6XBSD949204
3DY14 0JBYes52.409181-2.5448183630361110SO630791ShropshireBD12 8DWSE151279
4EX22 7NBYes50.849725-4.33216235926108170SS359081DevonHD8 8EBSE199099
5OL14 6XBNo53.680058-2.078324394924420402SD949204West YorkshireHD7 6DBSE048115
6BD12 8DWNo53.747261-1.772511415100427900SE151279West Yorkshire
7PL18 9HRYes50.515922-4.2285424210270831SX421708Cornwall
8SK6 7HWYes53.384991-2.058445396210387573SJ962875Greater Manchester
9TN27 0HWYes51.2267630.781863594335151185TQ943511Kent
10BB1 8JRYes53.761038-2.495776367413429523SD674295Lancashire
11NE19 2LSYes55.089229-2.012488399301577199NY993771Northumberland
12BA4 4FGYes51.189244-2.4763366810143443ST668434Somerset
13CR3 9BSNo51.305874-0.056082535600158100TQ356581Surrey
14DH9 6TQYes54.866323-1.688048420120552438NZ201524County Durham
15ST1 2HTYes53.029293-2.170013388695348014SJ886480Staffordshire
16RG9 6YJNo51.593958-0.914808475269188903SU752889Buckinghamshire
17TN16 3DXYes51.3151140.033652541826159297TQ418592Greater London
18BB8 0PGYes53.857663-2.157957389708440171SD897401Lancashire
19DL6 3AQYes54.372545-1.289145446278497681SE462976North Yorkshire
20ST8 6JAYes53.11329-2.174573388412357359SJ884573Staffordshire
21B31 5HYYes52.401582-1.998472400201278174SP002781West Midlands
22HD8 8EBNo53.585907-1.699963419960409966SE199099West Yorkshire
23PL32 9RHYes50.587138-4.70173920884779863SX088798Cornwall
24HD7 6DBYes53.600607-1.92882404808411562SE048115West Yorkshire
25BB12 9QJYes53.83691-2.256667383207437881SD832378Lancashire
26
Main
Cell Formulas
RangeFormula
K2:L5K2=FILTER(FILTER(A1:H25,H1:H25=J2),COUNTIF(K1:L1,A1:H1))
Dynamic array formulas.
 
Upvote 0
Hi,

Thank you for the response. I have tried the function and it's not working. In your example I would want to only update data for columns A, C, D, and with the option of filtering on "Yes" or "No". Is that clear? So maybe Yes and No is a button that I can select or a drop-down cell, and when yes is selected then the sheet is automatically updated.
 
Upvote 0
Hi, I get the below error;
1606312904224.png
 
Upvote 0
Fluff said:
In what way?
Click to expand...
To start off with, I cannot find the filter formula in excel. And even when I manually type in the formula, I get an error that says "That function isn't valid."
 
Upvote 0
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyPostcodeLatitudeLongitude
2CA11 0SAYes54.615912-2.908883341403524908NY414249CumbriaNoOL14 6XB53.68006-2.07832
3DY14 0JBYes52.409181-2.5448183630361110SO630791ShropshireBD12 8DW53.74726-1.77251
4EX22 7NBYes50.849725-4.33216235926108170SS359081DevonCR3 9BS51.30587-0.05608
5OL14 6XBNo53.680058-2.078324394924420402SD949204West YorkshireRG9 6YJ51.59396-0.91481
6BD12 8DWNo53.747261-1.772511415100427900SE151279West YorkshireHD8 8EB53.58591-1.69996
7PL18 9HRYes50.515922-4.2285424210270831SX421708Cornwall   
8SK6 7HWYes53.384991-2.058445396210387573SJ962875Greater Manchester   
9TN27 0HWYes51.2267630.781863594335151185TQ943511Kent   
10BB1 8JRYes53.761038-2.495776367413429523SD674295Lancashire   
11NE19 2LSYes55.089229-2.012488399301577199NY993771Northumberland   
12BA4 4FGYes51.189244-2.4763366810143443ST668434Somerset   
13CR3 9BSNo51.305874-0.056082535600158100TQ356581Surrey   
14DH9 6TQYes54.866323-1.688048420120552438NZ201524County Durham   
15ST1 2HTYes53.029293-2.170013388695348014SJ886480Staffordshire   
16RG9 6YJNo51.593958-0.914808475269188903SU752889Buckinghamshire   
17TN16 3DXYes51.3151140.033652541826159297TQ418592Greater London   
18BB8 0PGYes53.857663-2.157957389708440171SD897401Lancashire   
19DL6 3AQYes54.372545-1.289145446278497681SE462976North Yorkshire   
20ST8 6JAYes53.11329-2.174573388412357359SJ884573Staffordshire   
21B31 5HYYes52.401582-1.998472400201278174SP002781West Midlands   
22HD8 8EBNo53.585907-1.699963419960409966SE199099West Yorkshire   
23PL32 9RHYes50.587138-4.70173920884779863SX088798Cornwall   
24HD7 6DBYes53.600607-1.92882404808411562SE048115West Yorkshire   
25BB12 9QJYes53.83691-2.256667383207437881SD832378Lancashire   
26
27
Main
Cell Formulas
RangeFormula
K2:K25K2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$J$2),ROWS($K$2:$K2))),"")
L2:M25L2=IFERROR(INDEX(C$2:C$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$J$2),ROWS($K$2:$K2))),"")
 
Upvote 0
Fluff said:
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyPostcodeLatitudeLongitude
2CA11 0SAYes54.615912-2.908883341403524908NY414249CumbriaNoOL14 6XB53.68006-2.07832
3DY14 0JBYes52.409181-2.5448183630361110SO630791ShropshireBD12 8DW53.74726-1.77251
4EX22 7NBYes50.849725-4.33216235926108170SS359081DevonCR3 9BS51.30587-0.05608
5OL14 6XBNo53.680058-2.078324394924420402SD949204West YorkshireRG9 6YJ51.59396-0.91481
6BD12 8DWNo53.747261-1.772511415100427900SE151279West YorkshireHD8 8EB53.58591-1.69996
7PL18 9HRYes50.515922-4.2285424210270831SX421708Cornwall   
8SK6 7HWYes53.384991-2.058445396210387573SJ962875Greater Manchester   
9TN27 0HWYes51.2267630.781863594335151185TQ943511Kent   
10BB1 8JRYes53.761038-2.495776367413429523SD674295Lancashire   
11NE19 2LSYes55.089229-2.012488399301577199NY993771Northumberland   
12BA4 4FGYes51.189244-2.4763366810143443ST668434Somerset   
13CR3 9BSNo51.305874-0.056082535600158100TQ356581Surrey   
14DH9 6TQYes54.866323-1.688048420120552438NZ201524County Durham   
15ST1 2HTYes53.029293-2.170013388695348014SJ886480Staffordshire   
16RG9 6YJNo51.593958-0.914808475269188903SU752889Buckinghamshire   
17TN16 3DXYes51.3151140.033652541826159297TQ418592Greater London   
18BB8 0PGYes53.857663-2.157957389708440171SD897401Lancashire   
19DL6 3AQYes54.372545-1.289145446278497681SE462976North Yorkshire   
20ST8 6JAYes53.11329-2.174573388412357359SJ884573Staffordshire   
21B31 5HYYes52.401582-1.998472400201278174SP002781West Midlands   
22HD8 8EBNo53.585907-1.699963419960409966SE199099West Yorkshire   
23PL32 9RHYes50.587138-4.70173920884779863SX088798Cornwall   
24HD7 6DBYes53.600607-1.92882404808411562SE048115West Yorkshire   
25BB12 9QJYes53.83691-2.256667383207437881SD832378Lancashire   
26
27
Main
Cell Formulas
RangeFormula
K2:K25K2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$J$2),ROWS($K$2:$K2))),"")
L2:M25L2=IFERROR(INDEX(C$2:C$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100=$J$2),ROWS($K$2:$K2))),"")
Click to expand...
This seems to work, can I ask a question (I hope it is not dumb). What is the (15,6) after the aggregate function?
 
Upvote 0
The 15 tells the function to act like the Small function & the 6 tells it to ignores errors.
 
Upvote 0
Fluff,

Thank you very much. It worked. An additional question (I owe you a million, you have no idea how much you have saved me). My master table data is request information, where the same person can request more than one item in the same request. Would I be able to in the summary page, that you helped me with, only see one line item for a requestor? So this is what I am currently seeing
1606317224898.png


and this is what I want to see, ideally:

1606317281115.png


Would I be able to incorporate this into my existing formula? Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
264
Alex Blakenburg
A
Y
  • Question
Mulitple index and match
Replies
1
Views
219
etaf
etaf
N
  • Question
find data containing key words
2
Replies
11
Views
252
NdNoviceHlp
NdNoviceHlp
P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
136
purekarting
P
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
237
bobaol
B

Forum statistics

Threads
1,214,376
Messages
6,119,172
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top