MrExcel LIVe, The 54 Greatest Tips of All Time. So from that pivot table, go to the PivotTable Tools-Īnalyze, Filter Connections, and hook that pivot table up to the Sector Filter.Īnd then as we choose, you see that this pivot table is updating, and that Okay, so, just do a test here- choose, for example,Ĭonsulting, and you see that this pivot table is updating but that pivot table New slicer based on the Slicer Source, field is Sector, we get our slicer here,Ĭhange the color if you'd like. That slicer is going to be based on the Slicer Source. The slicer's not going to be based on the Sales table, nor the Prospects table Now, what I want to do is insert a slicer, but Okay, beautiful, that's all working great. We'll put the Sector here so we can see how many prospects there were in each If I go into Field Settings, because I'm using the Data Model, I have an extraĬalculation here at the bottom called Count- Distinct Count. It starts out with a count of prospects, but So, from here- Insert, PivotTable, again make sure to "Add thisĭata to the Data Model", this time I'll put it on the same sheet, so weĬan kind of see how they are interacting with each other. Then, I want to build a second pivot table that's using the secondĭata set. Want to narrow it down to just the top 5, or top 3, or something like that. I want to see this high to low- so Data, Z to A, and I Start here, Insert, PivotTable, make sure to check the box for "Add thisĭata to the Data Model, and we can build a nice report of Customer, and maybe Now, at this point, I can build my two pivot tables. Relationship is, both from this one to the Slicer Source, and from this one to Now createĪnother Relationship from the right side, from the Prospect table- it has aįield called Sector, it's related to Slicer Source, the field called Sector, Sector that's related to the Slicer Source- Sector, click OK. Going to create a New Relationship from the Sales table. So we come to Relationships- Data, Relationships, and I'm Somehow teach Excel that this table is related to both this table and this So, I have the three tables and I need to Going to call the middle one my Slicer Source, and then this one over here I'll Names, like "Table 1", "Table 2", and "Table 3",Īnd I'm going to rename those- I'm going to call this left one Sales, I'm Second one, Ctrl+T, "My table has headers, Yes third one, Ctrl+T, So I take the left one, Ctrl+T "My table has headers", Yes Take each of these tables and make them into- Format as Table using Ctrl+T,Īlright. Take the Sector's from the right table, paste all of these together, and thenĬhoose the whole set, and under Data, choose Remove Duplicates- right here-Īnd we end up with just the unique list of Sectors. So I take the Sectors from the left table, I Tables, and that table is just going to be really simple- it's just going toīe a list of all of the Sectors. We're going to build a third table that's going to live between the other two Tables- I need to just completely start over. And if I have anyĮxisting pivot tables on these two data sets, I need to get rid of those pivot Has Sector, and I have a table on the right that has Sector. And so, you know, I wondered if there was an easier way to doĪnd so, I have a table here on the left that Really caused a lot of problems because people have slicers based on data thatĭoesn't match. Question, he said, "Hey, look, I have pivot tables built on two differentĭata sets and I'd like one slicer to be able to control those." And now Iĭid a video about this- Episode 2104- that used some VBA, but this video has I was in New Jersey doing a seminar there, and Rick asked a Hey, welcome back to the MrExcel netcast, I'mīill Jelen. Learn Excel from MrExcel Podcast, Episode This method seems far easier than the VBA method described in video 2104. The result will be two pivot tables (from different data sets) that react to the slicer. Select the other pivot table and choose Filter Connections.Ĭonnect the other pivot table to the slicer Initially, only one pivot table will respond to the slicer. Find the Slicer Source table and build the slicer from there.
When you insert a slicer, there will be two tabs across the top. When you create a pivot table from each of the two data sets, be sure to check the box for Add This Data to the Data Model. Use Data, Remove Duplicates to create a unique list of the sectors found in either table.īuild a third table to be the source for the slicer
If both of your data sets contain a field called Sector and you want the pivot table to be based on Sector, copy the Sectors from both tables to a new table. The key step is to build a new SlicerSource table. The data model is easier than the VBA solution.
0 kommentar(er)
