slicer to multiple tables

R

Rubixx

New Member
Joined
Mar 9, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I am in the process of creating a large database for hardware. There is 1 spot where I need to have 2 different pivot tables filtered with the same slicer(s).

I was informed I need to learn power query and it was set up to do 1 filter but if I have to set up connections in this manor I will have to do multiple filters. I am not seeing how exactly the file was done.

I have the category list, and 2 tables tied to it through the relationships. but I cannot duplicate the process of filtering 2 different tables. If I combined them into 1 big table I would be adding roughly 60+ lines to thousands of variations thus making this list crazy large. Also somehow the person who helped make the table into a query had my information in the same tab, but everytime I turn my data into a table then data query it puts it into a new tab and I dont know why.

my ultimate goal is on the picture attached, 2 pivot tables 1 slicer, i will be doing this with multiple filters, just need to learn how to do the 1st one first.
 

Attachments

  • Slicer question2.PNG
    Slicer question2.PNG
    83.6 KB · Views: 13
Sort by date Sort by votes
I also deleted the filters that were on the sheet and used the one from Cat List instead.

So in this scenerio I need to make sure I add another category list (different name but same concept) for any additional filters
Pretty much. It should work that way, it's a bit of a workaround at times.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
ok I dont know I see everything your doing and for whatever reason I cannot duplicate it. I feel bad I feel like im wasting your time. Maybe we can simplify this?

is there a more simplified way of having these dimensions done the way you have it done without having to duplicate this, I have 3 categories I would like to have this filter by. my concern instead of just asking if you would be willing to set it up and I wont worry about it since itll be done, that if I mess it up on accident Ill screw the sheet up. This is the only area where I need a filter to connect between 2 tables, and sadly this is the only thing preventing me, as far as I can tell, from finishing this file. Although far from complete everything else can be done with simple slicers I believe. I might just have to little experience in this side of excel to really know whats going on.

if this is really the only way to accomplish this goal Ill just have to create a separate slicer and just have them filter manually, just trying to eliminate the possibility of mistakes if at all possible.
 
Upvote 0
Rubixx said:
ok I dont know I see everything your doing and for whatever reason I cannot duplicate it. I feel bad I feel like im wasting your time. Maybe we can simplify this?

is there a more simplified way of having these dimensions done the way you have it done without having to duplicate this, I have 3 categories I would like to have this filter by. my concern instead of just asking if you would be willing to set it up and I wont worry about it since itll be done, that if I mess it up on accident Ill screw the sheet up. This is the only area where I need a filter to connect between 2 tables, and sadly this is the only thing preventing me, as far as I can tell, from finishing this file. Although far from complete everything else can be done with simple slicers I believe. I might just have to little experience in this side of excel to really know whats going on.

if this is really the only way to accomplish this goal Ill just have to create a separate slicer and just have them filter manually, just trying to eliminate the possibility of mistakes if at all possible.
Click to expand...
or it is asking a lot but would you be able to walk me through it from scratch if we started from square one? I am sure you are busy so I fully understand if you wouldnt want to go that route.
 
Upvote 0
Going through it from scratch would probably invlove rebuilding the model by understanding the business, so it's not really an option.

Maybe reshare the file and explain what's not working the way you want.
 
Upvote 0
mrshl9898 said:
Going through it from scratch would probably invlove rebuilding the model by understanding the business, so it's not really an option.

Maybe reshare the file and explain what's not working the way you want.
Click to expand...
ok so I have 2 sheets here, the first 1 is the one you did and the 2nd one is new (i wasnt that far but I knew I had to tackle this issue). If you look on the new slicer project youll see the tabs dimension tab, and dimension type. ultimately those 3 filters in dimension tab I want to have a filter that will show type 1-9 (not all loaded) which the dimensions are broken down by type in dimension type.

All that was done on the original sheet was the main tabs were into tables, made into a query and then made the 1 connection.

Maybe im going about this wrong, I need to make my family breakdown into a table then pivot table that ill put on my slicer tab.

I originally had if bolt was filtered then both that and my dimensions would filter appropriately, of which you were able to create, my issue is I dont know what I was doing different I just cannot duplicate the process, with additional columns, for some reason. I was looking to duplicate the process (knowing how in case I did it in the future)....as im typing this out I think I have the answer although I still think im not sure I can do what process you did.

If I add the type as a column in my family breakdown, is there a way to add a pivot table that will just auto update with my main table without worrying which slicer you touch, what I mean is if you choose hex cap screw (under product name) it can only be a bolt, if I have a column in that line that associates that breakdown as type 1, can I have the 2nd table filter automatically without being in the same table as the first. sorry if thats confusing.

if you go off the first list, the one you sent back I tried to update type with hex head cap screw and other variables and created a query for just that type of item just like what was done with the cat list. I couldnt make it filter both the way you did, once I got the filter to work even with the values there I was only able to filter 1 not both tables. of which now I think might not work.

filetransfer.io

Download Data package from March 11th.

Free transfer of up to 6 GB of photos, videos and documents. Send large files via email or a link to share. No registration, no ads, just simple file sharing!
filetransfer.io filetransfer.io
 
Upvote 0
Rubixx said:
ok so I have 2 sheets here, the first 1 is the one you did and the 2nd one is new (i wasnt that far but I knew I had to tackle this issue). If you look on the new slicer project youll see the tabs dimension tab, and dimension type. ultimately those 3 filters in dimension tab I want to have a filter that will show type 1-9 (not all loaded) which the dimensions are broken down by type in dimension type.

All that was done on the original sheet was the main tabs were into tables, made into a query and then made the 1 connection.

Maybe im going about this wrong, I need to make my family breakdown into a table then pivot table that ill put on my slicer tab.

I originally had if bolt was filtered then both that and my dimensions would filter appropriately, of which you were able to create, my issue is I dont know what I was doing different I just cannot duplicate the process, with additional columns, for some reason. I was looking to duplicate the process (knowing how in case I did it in the future)....as im typing this out I think I have the answer although I still think im not sure I can do what process you did.

If I add the type as a column in my family breakdown, is there a way to add a pivot table that will just auto update with my main table without worrying which slicer you touch, what I mean is if you choose hex cap screw (under product name) it can only be a bolt, if I have a column in that line that associates that breakdown as type 1, can I have the 2nd table filter automatically without being in the same table as the first. sorry if thats confusing.

if you go off the first list, the one you sent back I tried to update type with hex head cap screw and other variables and created a query for just that type of item just like what was done with the cat list. I couldnt make it filter both the way you did, once I got the filter to work even with the values there I was only able to filter 1 not both tables. of which now I think might not work.

filetransfer.io

Download Data package from March 11th.

Free transfer of up to 6 GB of photos, videos and documents. Send large files via email or a link to share. No registration, no ads, just simple file sharing!
filetransfer.io filetransfer.io
Click to expand...
the people who will be using this wont know what is type 1 or type 2 that is why I am trying to make the connection so they cannot chose type 2 dimensions for a type 1 part
 
Upvote 0
Will this product/s

TypeProduct nameSAP Product nameType
BoltsHex Cap ScrewsHexagon head screw, inchtype 1

Have all these dimensions?

TypeDimension
type 10-80
type 11 1/2-12
type 11 1/2-6
type 11 1/2-8
type 11 1/4-12
type 11 1/4-7
type 11 1/4-8
type 11 1/8-12
type 11 1/8-7
type 11 1/8-8
type 11 3/4-12
type 11 3/4-5
type 11 3/4-8
type 11 3/8-12
type 11 3/8-6
type 11 3/8-8
type 11 5/8-12
type 11 5/8-5 1/2
type 11 5/8-8
type 11 7/8-12
type 11 7/8-5
type 11 7/8-8
type 11/2-13
type 11/2-20
type 11/4-20
type 11/4-28
type 110-24
type 110-32
type 11-12
type 11-14
type 112-24
type 112-28
type 11-64
type 11-72
type 11-8
type 12-4 1/2
type 12-4 12
type 12-56
type 12-64
type 12-8
type 13/4-10
type 13/4-16
type 13/8-16
type 13/8-24
type 13-48
type 13-56
type 14-40
type 14-48
type 15/16-18
type 15/16-24
type 15/8-11
type 15/8-18
type 15-40
type 15-44
type 16-32
type 16-40
type 17/16-14
type 17/16-20
type 17/8-14
type 17/8-9
type 18-32
type 18-36
type 19/16-12
type 19/16-18
 
Upvote 0
Why can't "Type" from "Dimension Tab" be added as a column to "Family Breakdown"?

"Dimension Type" just seems ambiguous to everything. Eg. 7/64 is Type 5 and Type 7. Therefore you can't look at a Dimension to determine the type or vice versa.
 
Upvote 0
Rubixx said:
If I add the type as a column in my family breakdown, is there a way to add a pivot table that will just auto update with my main table without worrying which slicer you touch
Click to expand...
Absolutely, just have the pivot table range be the whole columns, then on refresh it will pick up the new one.

Just put a text filter on does not contain blank.
 
Upvote 0
mrshl9898 said:
Absolutely, just have the pivot table range be the whole columns, then on refresh it will pick up the new one.
mrshl9898 said:
Will this product/s

TypeProduct nameSAP Product nameType
BoltsHex Cap ScrewsHexagon head screw, inchtype 1

Have all these dimensions?

TypeDimension
type 10-80
type 11 1/2-12
type 11 1/2-6
type 11 1/2-8
type 11 1/4-12
type 11 1/4-7
type 11 1/4-8
type 11 1/8-12
type 11 1/8-7
type 11 1/8-8
type 11 3/4-12
type 11 3/4-5
type 11 3/4-8
type 11 3/8-12
type 11 3/8-6
type 11 3/8-8
type 11 5/8-12
type 11 5/8-5 1/2
type 11 5/8-8
type 11 7/8-12
type 11 7/8-5
type 11 7/8-8
type 11/2-13
type 11/2-20
type 11/4-20
type 11/4-28
type 110-24
type 110-32
type 11-12
type 11-14
type 112-24
type 112-28
type 11-64
type 11-72
type 11-8
type 12-4 1/2
type 12-4 12
type 12-56
type 12-64
type 12-8
type 13/4-10
type 13/4-16
type 13/8-16
type 13/8-24
type 13-48
type 13-56
type 14-40
type 14-48
type 15/16-18
type 15/16-24
type 15/8-11
type 15/8-18
type 15-40
type 15-44
type 16-32
type 16-40
type 17/16-14
type 17/16-20
type 17/8-14
type 17/8-9
type 18-32
type 18-36
type 19/16-12
type 19/16-18
Click to expand...
Yes they could be any of those, when it comes to this product, each item can fall into 5 categories, coarse, fine, tapping, washer or plain diameter. The idea behind this is to build a description for my colleagues to use that would fill out what exactly our part should read like in our system. When it comes to these different categories they use different callouts, 1/4_20 is a coarse thread piece but the dimension would not exist for a tapping screw, where as it doesnt have a choice between dimensions it is just a 1/4 inch, but both are significant in they way they are presented. for people trying to cut corners, not know what they are looking for, or are still new to our new system (as I do implementations) they may think calling out 1/4 is enough when in fact you need that _20.

as for the other comment, let me see if I can do this correctly, and hopefully this will correct the issue, but just to clarify would this still be 2 pivot tables, we add type 1/2 etc...but have the second pivot which operates in the manor you had done? I would have to click on the slicer to choose type 1 still though, or am I incorrect or would it be 2 pivots out of the same table...not sure fi that even makes sense
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Pivot Table Slicers mystery!!!
Replies
9
Views
750
adambc
A
B
  • Question
Pivot Table question - could be impossible to implement
Replies
0
Views
511
buroh
B
R
  • Question
Power Query and Pivot Tables/Charts
Replies
0
Views
124
RodneyW
R
F
  • Question
Pivot table/Slicer static format?
Replies
0
Views
134
FGaxha
F
D
  • Question
Slicers/ formulas no longer working
Replies
4
Views
236
dd8
D

Forum statistics

Threads
1,214,386
Messages
6,119,214
Members
448,874
Latest member
b1step2far

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