Heinously difficult excel problem :-(

[jamesc1987]

Or so I believe it is!

In column A, I have a list of every country in the world, and regions within these countries.

In row 1, I have Months, starting in September 2008, going all the way to September 2016 (96 columns).

B2:CT387 contains my data, which is sourced automatically from IBM cognos. As such, I cannot alter the structure of the worksheet.

My data shows a sales percentage for a product I am analysing, in a given country, in any particular month.

I am creating charts which show how the sales percentage changes over time (simple enough). However, the chart should only show data from september 2008 through to the current month (again very simple If the data is static)

When we move forward a month, I would like my chart to source a new column, e.g. include an extra column to the right of the original source data. Is this possible? it would be a nightmare to update every country and region automatically every month.

Please help!!

 

[lenze]

HAve a look at Dynamic Named Ranges

lenze

 

[pgc01]

... and an example from Jon Peltier, with links to other examples:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

 

[jamesc1987]

Thanks Lenze,

It looks as though dynamic ranges are what I am looking for. My data starts in september 2008, and goes all the way to 2016. I would only like my charts to display september 2008 to whatever the current month is, so I assume I would use OFFSET and some kind of COUNTIF function. This is the bit I'm struggling with really, I'm a bit of an excel nube

 

[Steve=True]

Here is a video and step by step tutorial of what you are trying to do.

If your data only goes to the current month, then it will work.

Let me know if you need to determine the current month (i.e. you are using some sort of projections for future data).

http://www.exceldashboardtemplates.com/?p=688

Steve=True
www.exceldashboardtemplates.com