+ Reply to Thread
Results 1 to 2 of 2

Analysing data from different tabs

  1. #1
    Registered User
    Join Date
    11-17-2022
    Location
    Stockport
    MS-Off Ver
    2210
    Posts
    1

    Analysing data from different tabs

    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!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Analysing data from different tabs

    Hi trs_chris and welcome to the forum,

    Depending on your workbook there may be many ways to solve your problem. I first thought Power Query Append function would be the answer, but this is only available if you have that tool in your version of Excel.
    I believe you are looking for a topic of 3D formulas to see if it works for you. There is a site explaining them at:
    https://www.xelplus.com/excel-3d-formulas/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Analysing Array Data
    By Cerkit in forum Excel Programming / VBA / Macros
    Replies: 49
    Last Post: 03-18-2020, 12:52 PM
  2. Help Analysing Data using Excel
    By humdiddydum in forum Excel General
    Replies: 2
    Last Post: 09-27-2017, 09:24 AM
  3. Analysing and Interpreting Data
    By Ov3rW4tch in forum Excel General
    Replies: 6
    Last Post: 05-17-2017, 04:58 PM
  4. Analysing Data in Excel
    By KB2015 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-01-2016, 09:29 AM
  5. Analysing data
    By i_ujala in forum Excel General
    Replies: 1
    Last Post: 04-08-2007, 05:29 AM
  6. analysing data from files set
    By Massimiliano in forum Excel General
    Replies: 0
    Last Post: 11-08-2005, 10:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1