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?