+ Reply to Thread
Results 1 to 8 of 8

How to make a chart that pulls from the same cell on multiple sheets?

  1. #1
    Registered User
    Join Date
    06-09-2004
    Posts
    29

    How to make a chart that pulls from the same cell on multiple sheets?

    Howdy,

    I have a spreadsheet that I use to track numbers from a Help Desk Ticketing System. I have a tab for each month and each tab has the same data in the same cells. E.g. B15 is always the total tickets opened. B25 is always the total tickets closed, etc.

    I want to have a Dashboard tab that can make a chart that would show things like the tickets opened per month and pull from the B15 cell on each tab in the sheet. How would I go about setting that up?

    Also, if I can combine two pieces of data onto one chart to show Opened and Closer per month and pull from the proper cells on each tab, that'd be great too.

    Thanks for any pointers or tutorials you can direct me to.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    Quote from the Moderator:

    Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Registered User
    Join Date
    06-09-2004
    Posts
    29

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    sample attached
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,353

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    IMO, the easiest way is to have a range on the dashboard (hidden if you don't want to see it, or put it on another tab) that pulls the desired data into a single block. Exactly how to build this range will depend on a number of factors. For simplicity, if this was the only time I was going to build this (as a template), I would do something like:

    in dashboard A1:Bn:
    Please Login or Register  to view this content.
    Then use that range to build the chart.

    Another thought, I don't know if it would apply. If your source data were in a better database format, I could see a simple pivot table/chart doing the same thing. Unless there is some other reason that you MUST store the data by month like your sample shows, I think there could be many advantages to consider storing the data in a good database arrangement and pivoting off of that to get the monthly summaries and the overall summary that you are looking for.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    06-09-2004
    Posts
    29

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    Well, I'm a novice at Pivot Tables so I have one tab for each month. Every month I duplicate a tab, paste in the new data, and edit the pivot tables to reflect the current tab instead of the previous one.

    I guess I could just paste all the data in one giant tab and have the pilot table break the data out by month. Hmm. I guess I'll go hunt around for a tutorial on how to do that sort of thing. Then I could just have a Table tab and a Data tab.

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    What version of Excel do you use?

  7. #7
    Registered User
    Join Date
    06-09-2004
    Posts
    29

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    I use 2016/365

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: How to make a chart that pulls from the same cell on multiple sheets?

    My suggestion in the attachments. It is based on Power Query. When you add another sheet, you need to refresh the data in a file named Dashboard.
    Click Data Tab and continue Show Query (for correct operation change file path)
    Attached Files Attached Files

+ 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. Creating a formula that pulls information off of many duplicated sheets
    By nrsensabaugh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2017, 06:52 PM
  2. Need to make a workbook that pulls and organizes data froma master sheet
    By cvnhrman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2015, 06:15 PM
  3. Replies: 3
    Last Post: 01-14-2014, 12:56 PM
  4. [SOLVED] Housekeeping payroll that pulls rows of information to different sheets
    By alphonse in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-29-2013, 09:45 AM
  5. Make a chart with data from several sheets
    By durbancic in forum Excel General
    Replies: 2
    Last Post: 03-22-2009, 02:21 PM
  6. Formula that pulls a factor from a chart on sheet 2
    By davidtown23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2007, 06:53 PM
  7. chart data from same cell on multiple sheets
    By drhamann in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-14-2005, 08:06 PM

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