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.

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.

sample attached

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.

What version of Excel do you use?

I use 2016/365

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)