Tornado/Sensitivity Chart

Sorry for the length of this...
I am trying to use a stacked bar chart to create a Sensitivity/Tornado chart.
I need this chart to be able to accept variable data (this is part of a large Excel workbook that users change data in). The data can be positive or negative (or span between).

The users would like to combine two sets of data (one % and one $) into one chart - so a Primary and Secondary axis are needed (however if this cannot be done, then I would just create two seperate ones).

I can create the automated chart, with two axis (using VBA code behind for some settings) in the positive number world. I create a relationship between the two sets of data (axis) and then set my min and max values, axes crossing line, and major gridlines based on this relationship. Reloading data is fine so long as it is positve.

Once I started adding negative numbers I discovered the 3 points of data would not work and was told to I would need 7 data sets, 4 sets are blank and 3 sets have fill.
I attempted to apply this however I am not sure how I could automatically determine which items should have fill and which shouldn't.

Given this data:
Expected amount: 30
Item#____Low____High
Num1____-80____-25
Num2____-50____25
Num3____25____100

I believe I need this layout of the 7 points:
Item#___data1__data2__data3__data4__data5__data6__data7
Num1___ -25___-55_____-80
Num2_________-50_____-50_________________25_____25
Num3________________________25____75___________100

I assume I would have to programmatically fill and not fill the correct bars but I am not sure how to do that.
I was hoping to find the answer in this thread but Steve never posted his solution/example.

Not sure if this helps but here is the code I use to set the chart settings on the two axes chart:

    'Set the $ data title
    ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "NPV - Expected Value: " & ActiveSheet.Range("AF10").Text
    
    'Set the % data title
    ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "IRR - Expected Value : " & ActiveSheet.Range("AK10").Text
    
    'Set the minimum and maximum ranges for the $ data,
    '  Also have it create the major unit lines automatically and set the Expected Value line for $ data
    With ActiveChart.Axes(xlValue, xlPrimary)
        .MinimumScale = Range("RngChrt1MinNPV")
        .MaximumScale = Range("RngChrt1MaxNPV")
        .MajorUnitIsAuto = False
        .MajorUnit = Range("RngChrt1NPVUnit")
        .Crosses = xlCustom
        .CrossesAt = Range("RngChartNPVExp")
    End With
    
    'Set the minimum and maximum ranges for the $ data,
    '  Also have it create the major unit lines automatically and set the Expected Value line for % data
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScale = Range("RngChrt1MinIRR")
        .MaximumScale = Range("RngChrt1MaxIRR")
        .MajorUnitIsAuto = False
        .MajorUnit = Range("RngChrt1IRRUnit")
    End With

Sorry for the late reply. I did create a solution (but in two separate graphs). I will try to clean it up for re-use and post it.

Originally Posted by wb4syth

Sorry for the late reply. I did create a solution (but in two separate graphs). I will try to clean it up for re-use and post it.

This is also something i'm working on.
I have a good working tornado, but I want another line that goes through my base point, and datalabels showing my base point for each Item.

I have attached an example and manually put in the '500' where I want the datalabel to go, and the line should run through the middle of it, any ideas?