Dynamic Chart with Combo Box

[Shaun]

Hi All

I have some data which I am trying to chart which is spread across 3 revenue centres:

• Property Sales
• Project Sales
• Property Management

Each of which has a series of associated costs.

What I am trying to do is create a dynamic chart which is dynamic in relation:

• Months Charted - already completed using a scroll bar
  
• Revenue Centre - user can select
• Associated Cost - user can select

I have used Name Ranges, but I cannot get the named ranges to link to the chart through combo boxes. I am struggling to understand what it is I am missing.

The basic chart is the same for all possible combinations - Months, Sales, Cost. I will be adding and changing things once I understand the process to prepare the charts.

Is anyone able to help me out please?

I have attached an example file.

 

[p45cal]

In the attached I have:

1. Changed the dropdowns on sheet Chart listfill properties to column A of sheet Calculations. A quick and dirty solution for the moment - you can refine.
2. Added a couple of MATCH formulae in S1:S2 of Chart.
3. Added a couple of dynamic named ranges Plot1 and Plot2 which use S1:S2 above as offsets to determine what the chart is plotting. Like this:=OFFSET(Calculations!$C$6:$N$6,Chart!$S$1,0) [You could remove the need to have the formulae in S1:S2 by including that formula in the dynamic range formula: =OFFSET(Calculations!$C$6:$N$6,MATCH(Chart!$Q$1,Calculations!$A$7:$A$32,0),0)]
4. Changed what the 2 series in the chart plot to Plot1 and Plot2 (Excel insists you include the file name when you want it to plot a named range such as these).

This is quite hard work so…

I've also added, to illustrate another way, a couple of sheets (the blue tabs Sheet4 and Sheet5). On Sheet4, I've created a table by re-arranging the data from the Data sheet (I had to change the dates from row 10 to real Excel dates) (By the way, I suspect the data on the Data sheet is from a report or perhaps a pivot table - it would be more robust if you could get the data directly from where this report has been created as it is probably in this format already)

I've created a pivot table-and-chart on Sheet5 from this re-arranged data and set it up to mirror what you had charted in your example file.

I've added some grouping (Account2) to match your grouping of data on the Calculations sheet (you may not want or need this)

Play around with the chart/pivot table, click on the small plus/minus buttons, filter the dates (there are loads of ways)

Hopefully of some use…

 

[Shaun]

Hi p45cal

Thank you for looking at the file for me.

I haven't yet had the opportunity to see what you have done, but I thought I would post a quick reply to say thank you.

You are correct, the data is a direct dump from an accounting package.

Cheers

Shaun