Charting in Excel from drop down menu selecti

Hello all, I have a Microsoft Office 2007 Charting question:

I have several categories of data regarding my production of widgets: Quantity on hand, Sales Quantity, Fill Rate, Dollar Value of inventory, etc. This data is extracted from numerous sources and compiles into one master excel worksheet at the end of each month. Because of how the data is populated in the master worksheet, I currently copy & paste the data categories for each chart so the layout is compliant with the chart’s selected data (i.e Month title in column A, Category data in column B, C, D, etc.)


The issue I am having is that I am currently creating and updating almost a dozen charts each month because different departments within my company want to view the data in different ways. [Ex. one department wants to view: Quantity on hand against the Dollar Value of inventory, while another department wants to view: Quantity on hand against Sales Quantity.]

Most of the charts are the same format (x-axis COLUMN chart with a Y-axis LINE chart) which is very simple. It just seems there would be an option available which would enable the user to define what data they want to populate the different axis' from a drop down menu (instead of them having to go into 'selecting the source data' itself). Currently I am stuck between copying & pasting the selected raw data into a format which is easy to chart… this can take me a couple hours each month.

I will continue to search on-line for an answer but would appreciate any help or guidance.
Thank you kindly!!!
-Netscur


By the way, the raw data gets complicated to work with, or 'select" because there are 4 manufacturing plants within my company. Most of the charts only want to focus on a single plant's data, or take a higher level view of all the plants. If anyone out there really wants to assist me but needs more information (examples), contact me directly and I will provide 'non-proprietary' examples.