Automating a data table for Actual/Forecast Chart

[KansaiDorifto]

Hello all

I am trying to create a chart like below. Actual numbers as a solid line and forecast numbers as a dotted line.

I have used formulas to automate the table below that draws numbers:

My formula is "=IF(C$3="Actual", B30 / 1000, "")", trying to remove the blanks. But the chart ends up treating all blanks as zeros looking like below:

I can manually delete all zero values and the chart works, but I would like it automated. Is there a way to automatically tell excel to not show the zeros?

Thanks

 

[Marius44]

HI
In your formula instead of double quotes put NON.DISP().
If I'm not mistaken in English it should be #N/A

HI,
Mario

 

[KansaiDorifto]

HI
In your formula instead of double quotes put NON.DISP().
If I'm not mistaken in English it should be #N/A

HI,
Mario

Thanks for the suggestion Mario. However it doesn't work as it changes the result to #Name?

 

[Marius44]

Hi
Please, insert your file (without sensitive datas)

Hi,
Mario

 

[KansaiDorifto]

Hi
Please, insert your file (without sensitive datas)

Hi,
Mario

Hi Mario

I am unable to insert my file here. Please see the screenshots below:

 

[Marius44]

HI
Excuse me. I understood something else.
To do what you ask you need VBA. In the file I attach, try changing the date in the yellow cell and you will see that the graph adjusts.
Click on this link to get my work file.
Let me know. HI,
Mario

 

[Fluff]

@Marius44 Please post your code to the site as per Rule#4 Thanks

 

[Marius44]

HI
I humbly apologize for the forgetfulness.
This is the code to insert into the "Foglio1" module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then
  Dim vAddress As Range, i As Long, dt As Double, pt As Double
  dt = Cells(1, 2).Value
  ActiveSheet.ChartObjects(1).Activate
  With ActiveChart.SeriesCollection(1)
    Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
    For i = 1 To vAddress.Cells.Count
      pt = Cells(3, i + 1)
      If pt > dt Then
        .Points(i).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
        .Points(i).Format.Line.DashStyle = msoLineDashDotDot
      Else
        .Points(i).Format.Line.ForeColor.RGB = RGB(0, 0, 255)
        .Points(i).Format.Line.DashStyle = msoLineSingle
      End If
    Next i
  End With
  Cells(1, 1).Select
  End If
End Sub

Hio,
Mario