Data filtering from Master tab to multiple tabs

L

LouisT

Board Regular
Joined
Apr 5, 2010
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone, I'm trying to filter data from one tab to multiple tabs based on a specific value on Tab 1. In this case on a person's name. If the data in the row in question has a leader's name, then pull all that data in that row to the appropriate tab. There would be multiple columns to pull. I've thought of just using a Vlookup however i'm sure there's a more efficient formula.

any help would be appreciated.

Thanks in advance
LouisT
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Hi LouisT

I'm not clear from your description as to exactly what you're trying to do. There are several possibilities depending on your answers to the following questions:


  1. Are you able to post a mock up of your Tab 1 showing the data layout, and an example of what you want to find and do once you've found it?
  2. What do the other tabs represent?
  3. Do all tabs (including Tab1) have the same column layout?
  4. Are they all independent of each other (i.e. different data from tab 1 reflected on each one?)
 
Upvote 0
Hi Col,
thanks for the reply.

Tab 1 would have multiple columns but to answer question 3 first, yes all tabs will have the same layout. think of it as having 1 tab and using the "filter" option which i suggested but they want their own separate tabs...lol. Question 2 the tabs represent each Leader in our Call Centre and therefore each tab would pull ONLY the data from Tab 1 that pertains to that Leader. Questions 4, yes they're all independent of each other.
Here's what the data in Tab 1 would look like:
IDNameDateLeader
12345David Bowie01/01/2019Mac the knife
23456Axel Rose02/01/2019Slash
34567Garth Brooks03/01/2019Alan Jackson
45678Guy Lafleur04/01/2019Slash

<tbody>
</tbody>

So basically as Tab 1 gets more and more data added to it, i'd want each "Leader" to have their data populated on their respective Tab. So in the case of "Slash", he would have 2 lines in this example and the others only 1. ideally with no gap between lines.

does that help?

thanks in advance
Louis


Col Delane said:
Hi LouisT

I'm not clear from your description as to exactly what you're trying to do. There are several possibilities depending on your answers to the following questions:


  1. Are you able to post a mock up of your Tab 1 showing the data layout, and an example of what you want to find and do once you've found it?
  2. What do the other tabs represent?
  3. Do all tabs (including Tab1) have the same column layout?
  4. Are they all independent of each other (i.e. different data from tab 1 reflected on each one?)
Click to expand...
 
Upvote 0
Louis

Thanks for the info.

A few more questions:


  1. How often is Tab1 refreshed, and does this involve replacing previous data with the new or is it additive?
  2. Ditto for each Leaders sheet?
  3. What happens once the relevant data is replicated/copied to each Leaders sheet - is there then data entry against those items (so these items need to be retained), or is this data just for information purposes?
 
Upvote 0
Hey Col,

1. The data on Tab 1 refresh will be sporadic and not on any type of regular schedule. It will also be additive and will not replace the existing data in the sheet.
2. Yes, 'Ditto' for each Leaders Sheet
3. The leader sheets are simply for their visibility and information purposes.

Thanks
Louis
 
Upvote 0
Louis

Here's a formula based solution, using Excel Tables to enable automatic replication of the formulas should you need to expand the Leader tables. Unfortunately, I haven't designed this to automatically expand the Leader tables when they become fully populated with matching data from Tab1 - but I think you could overcome this easily by:

  1. having enough blank rows in the Leader tables to cater for growth in the foreseeable future
  2. when required, simply copying the Leader Index formula in column A of each Leader table down to add more rows and trigger automatic replication of the formulas in the other columns.

Note that each of these tables are Excel Tables but the formulas use ColumnRow references as the "compatability" feature was turned on in my XL2007 app. at the time - but when you build these in your app. you could ensure the Table field names get used instead.

I've included the Leader tables on the same sheet in my build, but they will work on separate sheets - you will just need to build the first sheet using the field names from the master table on Tab1, and then copy that "template" sheet for each other Leader, and change the leader'
s name currently shown in cells, A12, A21, and A30


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Formula​
Data​
Data​
Data​
Data​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Tab 1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Leader Index​
ID​
Name​
Date​
Leader​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
1​
12,345 ​
David Bowie​
01-01-19​
Mac the knife​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
1​
23,456 ​
Axel Rose​
02-01-19​
Slash​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
1​
34,567 ​
Garth Brooks​
03-01-19​
Alan Jackson​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
2​
45,678 ​
Guy Lafleur​
04-01-19​
Slash​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
Formula​
Formula​
Formula​
Formula​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
Mac the knife​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Leader Index[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
1​
12,345​
David Bowie​
01-01-19​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
2​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
3​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
4​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
5​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
Formula​
Formula​
Formula​
Formula​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
Slash​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Leader Index[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
1​
23,456​
Axel Rose​
02-01-19​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
2​
45,678​
Guy Lafleur​
04-01-19​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
3​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​
4​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
5​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​
Formula​
Formula​
Formula​
Formula​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR]​
Alan Jackson​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Leader Index[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR]​
1​
34,567​
Garth Brooks​
03-01-19​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR]​
2​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]34[/COLOR]​
3​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]35[/COLOR]​
4​
-​
-​
-​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]36[/COLOR]​
5​
-​
-​
-​

<tbody>
</tbody>

FORMULAS:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Formula​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Tab 1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Leader Index​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
= COUNTIFS( $E$4:$E5, $E5 )​

<tbody>
</tbody>


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
Formula​
Formula​
Formula​
Formula​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
Mac the knife​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Leader Index[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
= IF( ISNUMBER(A13), A13, 0 ) + 1​
= IFERROR( INDEX( $B$5:$B$9, MATCH( 1, ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ), 0 ) ), "-" )​
= IFERROR( INDEX( $C$5:$C$9, MATCH( 1, ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ), 0 ) ), "-" )​
= IFERROR( INDEX( $D$5:$D$9, MATCH( 1, ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ), 0 ) ), "-" )​

<tbody>
</tbody>

Note that the formulas in B14:D14 for 'Mac the knife' are array formulas (even though the {} on each end don't display here for some unknown reason) and must be entered with Ctl+Shift+Enter.

A non-array formula alternative is below, but because of the need to return a text value for the Name field, an more complex formula is required for that field.

B14​
C14​
D14​
= IFERROR( SUMPRODUCT( ( $B$5:$B$9) * ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ) ), "-" )​
= IFERROR( INDEX( C$5:$C$9, SUMPRODUCT( ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ) * ( ROW( $A$5:$A$9 ) - ROW($A$4) ) ), 1 ), "-" )​
= IFERROR( SUMPRODUCT( ( $D$5:$D$9) * ( $A$12 = $E$5:$E$9 ) * ( $A14 = $A$5:$A$9 ) ), "-" )​

<tbody>
</tbody>

You might also consider building some cross-checking formulas (using the COUNTIFS function) outside of the tables to check that:
  1. the same number of the populated items in the master table for a particular Leader are reflected in the Leader's table
  2. the count all items in the master table equals the sum of the counts of all populated items in all Leader tables.

It is not the most elegant solution but is also not that complex. Anyway, I hope it helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Counting total quantity of entries from multiple tabs
Replies
2
Views
114
joshbrfc
J
S
  • Question
Find exact name across multiple tabs & combine with data from the most recent tab?
Replies
0
Views
67
Sunshine8790
S
P
  • Question
2D spilled array, using REDUCE and INDIRECT to consolidate data from multiple sheets.
Replies
5
Views
275
Fluff
Fluff
Kaz09
  • Question
Copy rows of data from multiple worksheets to based on column equalling YES
Replies
0
Views
109
Kaz09
Kaz09
L
  • Question
Data Comparison Query - Comparing two tabs
Replies
5
Views
236
felixstraube
F

Forum statistics

Threads
1,214,987
Messages
6,122,613
Members
449,090
Latest member
vivek chauhan

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