Hello,
I have 3 worksheets: Budget, Forecast & Actuals.
I want to create a summary at the top of the Budget worksheet that allows user to select Customer/Category/Size. I thought the Slicer tool would work best but then realized I only know how to capture the data from the one worksheet.
How can I easily capture data from different worksheets with drop down option/filter?
I have attached a sample workbook.
Thank you so much!!
My suggestion in the attachment. Try to remodel your data. You have two variants to choose from.
Hi stasinek, thanks so much for your suggestion! However, I was trying to avoid combining the data onto one tab. My real data is actually really long
Is there any way to use the slicer from 3 worksheets?
Thanks!
In the information window is Ex 2003 and you are using slicers? Probably Ex 2013 so you can safely use Power Query
I just installed PowerPivot but have no idea where to start! I definitely want to learn it in the future.
For now, I think I have no choice but to combine the 3 tabs into 1 then slice and subtotal.
Thanks for your suggestions!
Update your profile about actual excel version. will be easier for you and for us
Thanks Sandy. Just updated
Do you have a workaround for me without using PowerPivot (right now). I'm about to consolidate the data from the tabs but if you have a suggestion please let me know!
Let me read whole thread first
Re: Slicers & Tables - How to link Slicer to mulitple tables
Is that what you want?
You can add slicer(s) on summary tab.
There is "standard" PivotTable
Did you use the relationship tool?
When I went to create a relationship, I got the error that said "...relationship in pivot table can only be unique values". I basically gave up at that point. My data tables are identical with repeating values. I just have them created on 3 separate tabs so I can keep track of budget, forecast and actuals.
Maybe I am making it too complicated and should just consolidate everything onto 1 tab.
Re: Slicers & Tables - How to link Slicer to mulitple tables
Did you see word "standard" ? I didn't use PowerPivot
PivotTable on Summary tab sums the values of the three tables
use PivotTable and PivotChart Wizard
edit:
you can change names: Row to Materials and Column to Category
but you didn't answer: is that what you want to achieve?
just in case here is extended version
Last edited by sandy666; 11-24-2017 at 02:27 PM. Reason: extended version
Thank you Sandy! I'm going to give it a try.
Thanks again!!
With drop down lists it will be like this
Re: Slicers & Tables - How to link Slicer to mulitple tables
Or maybe like this one?
[@Summary2]
And it was supposed to be without PQ
Who said: without PQ?
Why do I have to complicate my life if it can be simplified?
Use Power (Query), Luke !
Please Login or Register to view this content.
Last edited by sandy666; 11-24-2017 at 07:40 PM.
greentea wrote "Do you have a workaround for me without using PowerPivot (right now)"
Sorry for off-topic interjection:Originally Posted by stasinek
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
PowerPivot is not a PowerQuery
PowerPivot is not a standard PivotTable
and vice versa
People are afraid of what they do not know and do not understand and they should learn it so the work with Excel would be easier.
Last edited by sandy666; 11-25-2017 at 09:29 AM.
No, the OP asked for it not to be using PowerPivot, not PowerQuery.And it was supposed to be without PQ
LOL, I've the support of beautiful Mod
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks