Pulling data from a master table into another sheet

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
31
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOP
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyPostcodeIn use?LatitudeLongitudeEastingNorthing
2CA11 0SATom54.615912-2.908883341403524908NY414249CumbriaCA11 0SATom54.61591-2.90888341403524908
3DY14 0JBTom52.409181-2.5448183630361110SO630791ShropshireSK6 7HWJerry53.38499-2.05845396210387573
4EX22 7NBTom50.849725-4.33216235926108170SS359081DevonST1 2HTSue53.02929-2.17001388695348014
5OL14 6XBTom53.680058-2.078324394924420402SD949204West YorkshireB31 5HYDave52.40158-1.99847400201278174
6BD12 8DWTom53.747261-1.772511415100427900SE151279West Yorkshire      
7PL18 9HRTom50.515922-4.2285424210270831SX421708Cornwall      
8SK6 7HWJerry53.384991-2.058445396210387573SJ962875Greater Manchester      
9TN27 0HWJerry51.2267630.781863594335151185TQ943511Kent      
10BB1 8JRJerry53.761038-2.495776367413429523SD674295Lancashire      
11NE19 2LSJerry55.089229-2.012488399301577199NY993771Northumberland      
12BA4 4FGJerry51.189244-2.4763366810143443ST668434Somerset      
13CR3 9BSJerry51.305874-0.056082535600158100TQ356581Surrey      
14DH9 6TQJerry54.866323-1.688048420120552438NZ201524County Durham      
15ST1 2HTSue53.029293-2.170013388695348014SJ886480Staffordshire      
16RG9 6YJSue51.593958-0.914808475269188903SU752889Buckinghamshire      
17TN16 3DXSue51.3151140.033652541826159297TQ418592Greater London      
18BB8 0PGSue53.857663-2.157957389708440171SD897401Lancashire      
19DL6 3AQSue54.372545-1.289145446278497681SE462976North Yorkshire      
20ST8 6JASue53.11329-2.174573388412357359SJ884573Staffordshire      
21B31 5HYDave52.401582-1.998472400201278174SP002781West Midlands      
22HD8 8EBDave53.585907-1.699963419960409966SE199099West Yorkshire      
23PL32 9RHDave50.587138-4.70173920884779863SX088798Cornwall      
24HD7 6DBDave53.600607-1.92882404808411562SE048115West Yorkshire      
25BB12 9QJDave53.83691-2.256667383207437881SD832378Lancashire      
26
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=L2)/(L2<>""),1)),"")
L2:L25L2=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:P25M2=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)),"")
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Tatum2020

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

1606324908118.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Which column contains the value to filter on?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Tatum2020

New Member
Joined
Nov 25, 2020
Messages
31
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Apologies, I have corrected it now: So B17 would reference column F in my master table.

1606330731454.png
 

Attachments

  • 1606330697984.png
    1606330697984.png
    57.6 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOP
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyPostcodeIn use?LatitudeLongitudeEastingNorthing
2CA11 0SATom54.615912-2.908883341403SourceNY414249CumbriaStoreEX22 7NBTom50.84973-4.33216235926store
3DY14 0JBTom52.409181-2.544818363036SourceSO630791ShropshireTN27 0HWJerry51.226760.781863594335Store
4EX22 7NBTom50.849725-4.33216235926storeSS359081DevonST1 2HTSue53.02929-2.17001388695Store
5OL14 6XBTom53.680058-2.078324394924SourceSD949204West YorkshireB31 5HYDave52.40158-1.99847400201Store
6BD12 8DWTom53.747261-1.772511415100SourceSE151279West Yorkshire      
7PL18 9HRTom50.515922-4.22854242102SourceSX421708Cornwall      
8SK6 7HWJerry53.384991-2.058445396210SourceSJ962875Greater Manchester      
9TN27 0HWJerry51.2267630.781863594335StoreTQ943511Kent      
10BB1 8JRJerry53.761038-2.495776367413StoreSD674295Lancashire      
11NE19 2LSJerry55.089229-2.012488399301StoreNY993771Northumberland      
12BA4 4FGJerry51.189244-2.4763366810StoreST668434Somerset      
13CR3 9BSJerry51.305874-0.056082535600StoreTQ356581Surrey      
14DH9 6TQJerry54.866323-1.688048420120StoreNZ201524County Durham      
15ST1 2HTSue53.029293-2.170013388695StoreSJ886480Staffordshire      
16RG9 6YJSue51.593958-0.914808475269StoreSU752889Buckinghamshire      
17TN16 3DXSue51.3151140.033652541826StoreTQ418592Greater London      
18BB8 0PGSue53.857663-2.157957389708StoreSD897401Lancashire      
19DL6 3AQSue54.372545-1.289145446278StoreSE462976North Yorkshire      
20ST8 6JASue53.11329-2.174573388412StoreSJ884573Staffordshire      
21B31 5HYDave52.401582-1.998472400201StoreSP002781West Midlands      
22HD8 8EBDave53.585907-1.699963419960StoreSE199099West Yorkshire      
23PL32 9RHDave50.587138-4.701739208847StoreSX088798Cornwall      
24HD7 6DBDave53.600607-1.92882404808StoreSE048115West Yorkshire      
25BB12 9QJDave53.83691-2.256667383207StoreSD832378Lancashire      
26
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=L2)/($F$2:$F$100=$J$2),1)),"")
L2:L25L2=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:P25M2=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)),"")
 
Solution

Tatum2020

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
As you don't have the Filter function, then you will need to drag the formula down.
 

Tatum2020

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

Watch MrExcel Video

Forum statistics

Threads
1,127,552
Messages
5,625,463
Members
416,107
Latest member
AVaes

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
Top