Chart: How to select Data Source (range) in an 'intelligent' way?

I am exporting some data to an excel Spreadsheet. They have headers(column names) (ex: M1, M2, M5, P4, P7, Z5, Z10).

I would like to plot data that share similar traits. For instance, I would like to have a chart with data whose headers start with M or data whose headers contain the number 5, etc...

How can I include a formula in the chart data range, so that the chart knows automatically which columns to select without me having to manually go in and select the data each time I export new data in my spreadsheet?

Note: as you can imagine, column with similar traits are not necessarily close to each other, and also every time I update the spreadsheet, column names may change.

I appreciate any help.

EDIT: someone suggested using Excel Macros or pivot table... I haven't really used any of those before. Do you think this is feasible? And if so, which way is better, and how should I go about it?

Hi math8,

You can use a Pivot Table but you would still need to select the Headings yourself.

A Macro could do this by applying some formatting or logic such as sorting your headings & then allocating the data but you would need to understand how to write Macros.

Originally Posted by noboffinme

Hi math8,

You can use a Pivot Table but you would still need to select the Headings yourself.

A Macro could do this by applying some formatting or logic such as sorting your headings & then allocating the data but you would need to understand how to write Macros.

Thank you for your message. Could you give me an example of how to use a Pivot Table for this problem? Also, I have never written a Macro before. Is it difficult to learn? Could you give me an example on what type of Macro would be used for a problem like this?

Note: My Ultimate goal is to be able to plot, each time I have a new output exported to Excel. I would like to create a Template Excel workbook, this same template should be able to plot for both the outputs below (i.e. be able to plot for Output1, and also plot when Output1 is replaced by Output2):

Output1:

Formula:

Please Login or Register  to view this content.

and

Output2:

Formula:

Please Login or Register  to view this content.

Have a look at these 2 You Tube vids.

Pivot tables;
http://www.youtube.com/watch?v=i4dHTafGpKI

Learning VBA Macros;
http://www.youtube.com/watch?v=t1vdt0FddsE
This VBA video is very basic but will give you an idea of how VBA works.