Pauljbooth
New Member
- Joined
- Sep 22, 2016
- Messages
- 3
Morning All
So I have a issue im hoping / praying some one may help with as its driving me bonkers as I just cant seem to nail it. It will prob be a two second job for someone who knows how to do it lol.
I have two data sets within a workbook, using pivot I have pretty much got all the information I could want out of the two data sets however I need to be able to display them on another page in a visual form so I have been using Pivot Charts to do so and controlling the selection of department via a Slicer which works well when using only one of the data sets but I need to be able to control both sets of data from the Slicer.
I think its important to mention that each data set has duplicate values i.e. every transaction by an operator in the their various departments is recorded in one sheet and every time they login is in another so there is duplicate values so I cant create a relationship within the data model (which I know if I could would have sorted this in a minute.
)
So the only way I can see around this is to use VBA which I will be honest about I am no VBA coder but I have tried a few options that I have found on the web but to no avail.
What I have tried so far is creating the slicer and the first pivot which contains 98% of the data which works fine, I have a report filter on that pivot for the departments, I have then told the next cell to pull the data label from the report
So report filter for the main pivot is in cell B81 so C81 displays via =B81 and that shares the department I need as a cell value, however I need to then filter say Piviottable31 by that value, how can I do that?
Thanks in advance
Paul
So I have a issue im hoping / praying some one may help with as its driving me bonkers as I just cant seem to nail it. It will prob be a two second job for someone who knows how to do it lol.
I have two data sets within a workbook, using pivot I have pretty much got all the information I could want out of the two data sets however I need to be able to display them on another page in a visual form so I have been using Pivot Charts to do so and controlling the selection of department via a Slicer which works well when using only one of the data sets but I need to be able to control both sets of data from the Slicer.
I think its important to mention that each data set has duplicate values i.e. every transaction by an operator in the their various departments is recorded in one sheet and every time they login is in another so there is duplicate values so I cant create a relationship within the data model (which I know if I could would have sorted this in a minute.
)So the only way I can see around this is to use VBA which I will be honest about I am no VBA coder but I have tried a few options that I have found on the web but to no avail.
What I have tried so far is creating the slicer and the first pivot which contains 98% of the data which works fine, I have a report filter on that pivot for the departments, I have then told the next cell to pull the data label from the report
So report filter for the main pivot is in cell B81 so C81 displays via =B81 and that shares the department I need as a cell value, however I need to then filter say Piviottable31 by that value, how can I do that?
Thanks in advance
Paul
