Help with comparison chart and drop down menu in chart

[Ecel Dumbo]

Hello I am new to charting,

If any of you could kind refer the attached spreadsheet and help draft a graph that compares Actual vs Budget of NET IT Expense with Periods on Y axis. And to introduce a drop down menu in the graph to select the highlighed subtotals in grey (Net FTE Costs, Net people cost)

ANy help will be greatly appreciated.


Excel Dumbo

 

[Hui]

See attached
The chart uses Two Named Formula Actuals and Budget
Budget is simply offset 14 columns to the right of Actuals

Actuals is a simple Offset formula that looks up the index value choosen from the Drop down box and yses that to retries the offset value from a table of offsets below the chart

 

[Ecel Dumbo]

Thank you very much Hui. This is exactly what i wanted. if you could also please let me know how to bring in the drop down menu in chart

 

[Hui]

Select the Chart
Goto the Developer Tab
Select Insert, Drop Down (Form control)
Right click on it
Format

 

[Ecel Dumbo]

I got it Hui. Thanks. Can you please see the file I have saved in the link kindly advise where i went wrong.
Cant figure out the problem here.I have reached the last stage. The drop down menu is not pulling out the right numbers on the graph

http://speedy.sh/P6eXD/Graph-Query.xlsx

 

[Ecel Dumbo]

thanks a lot hui. i figured it out. thank heaps for your help

 

[Ecel Dumbo]

Hi Hui, Coming back to this again please. If you notice the attached file you had solved for me, in the Mthly tab, Row 2 , column O, P and Q show forecast , and the months before are actuals. I would like the budget remain as it is. I would like to show the bar columns for O, P and Q as dotted line to mean they are forecast

 

[Hui]

see attached
I am not using named formula now
I am using the Yellow area for the data

 

[Ecel Dumbo]

Thanks for this HUi. How did u prevent #N/A values from not appearing on graph ?

 

[Hui]

Excel does that
If I'd used 0's they would have displayed as 0 but #N/A is ignored

 

[Ecel Dumbo]

Thanks Hui but that does not seem to be working for me. I have attached my file here. Can you please look if you have some time. Thanks heaps for all your earlier help

 

[Hui]

Strange

The Actual and Forecast must be Stacked Column
The Budget must be on Axis 2
Then it seemed to just work

 

[Ecel Dumbo]

HI Hui, I saw the secondary series and applied it to the budget. But how do I make the bar appear inside or outside the actual bar ? that will look like a thermometer. At the momement my budget bar is not appearing within the actual bar

 

[Ecel Dumbo]

THanks Hui. I figured it out. Thanks heaps for all your help today and yesterday