Need help to set Chart to select data that doesnt have a fixed column position.

hye everyone,

I am creating a chart that pulls data from different columns. Usually the no. of columns dont change but when they do, the chart messes up.

Does anyone know a way to setup the chart in a way that it selects data in the whole column range based on the top header "name" instead of hard wired to seek e.g $c$1 all the time?

Use a secondary table from which you can drive your chart and put the data into that table from your main table, maybe using some form of selection criteria.

Hope this helps.

Pete

My macro is coded to take from that single table, if I make another table, im afraid it will mess up. The data is quite big. But I'll try your suggestion for now.

To elaborate.. referring to the code below, I'd like the chart to search for the header of the column instead of the column address. So it changes from

ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$5:$A$23" to something like
ActiveChart.SeriesCollection(1).XValues = = Sheet1! "Total Sales"

Please Login or Register  to view this content.