Hi, I'm working on a concept at the moment and don't really have a dummy sheet to share, but I've just been bashing together a few different formula and brushing up on how to achieve what I need.
I'm looking to present some data from multiple tabs visually.
Each tab of the sheet will represent data for a given month. Within a given tab, the headers will all be identical, and I'll just be dumping/pasting data from another place into a new tab every month.
So the concept is to have a dashboard page which can easily round up data from each tab.
My assumption is, the dashboard will live on a new tab, and I'll need to create pivot tables to round up the data and run charts to visualise it.
I'll need to point the pivot table at the appropriate tab and that's where I'm coming unstuck, because I'd like to be able to select any month/tab of my choosing on a whim.
I figured this could be done using a drop-down menu, simply containing the 12 months of the year, and that I'd find a means to have the pivot table data query adapt based upon the month I select - ultimately so that if the drop-down says January, it'll find the January tab and pull in the relevant data from the referenced cells.
I don't have any real experience creating pivot tables, but lots of fairly basic user knowledge on Excel, so I didn't expect this to be difficult.
But I can't find a means to have the pivot table accept a named range or indirect reference as its data source, and am now questioning the premise of my approach.
Can anybody provide some pointers - am I tackling this in the right way and maybe just having some issues of syntax, or do I need to rethink the entire method?
I expect its somewhat relevant that I'm creating the tabs on a month to month basis. I could create each month in advance but figured that it would just error if I selected a month which doesn't yet exist, and I can probably hide the errors... but I'm not close to reaching that point yet.
Thanks in advance!
Bookmarks