How I can set dynamic pivot table ONLY when I have enough data point

[vincentwady]

Hi,

I'm trying to create a dynamic range of pivot table chart, only when I haveenough data point. For example, My column A is Date, column B is return. If I have less than 13 months of return, I don't want the pivot table chart to be created. Once I have more than 13 data point, then I want the pivot table chart populated. I have set a column name by using the range like below:

=OFFSET(Sheet1!$A$1,0,0,IF(COUNTA(Sheet1!$A$2:$A$23)<13,2,COUNTA(Sheet1!$A$2:$A$23)+1),2)

But it will still show the pivot table and chart if I have less than 13 data points. Is there a way to achieve my goal through macro?

Thanks,
Vincent

 

[Ben McClave]

Vincent,

This is a simple solution, but might work for you. Place an image over the chart such that the chart can't be seen (image could be a simple white background or even a message such as "Not enough data").

Then, place this code in the worksheet's Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.Shapes("Image1").Visible = WorksheetFunction.CountA(Range("$A$2:$A$23")) < 13

End Sub