Dynamic chart automatically changing its range if we select wrong date

[maniknandi]

I have prepared a dynamic chart, we can select month, year and sales person. The chart is automatically generated monthly sales done by the sales persons . The starting date is 7-Apr-12 and end date is 31-Mar-13.


The problem is that if we put the month and date not in our date range, the chart lost its data range.

https://www.dropbox.com/s/jts27btd9go8xds/Sales Analysis Database.xlsx

Please help me and thanks in advance.

 

[Smallman]

Maniknandi


I thought I cracked this one for you a few days ago. File can be found here;


http://rapidshare.com/files/3898193334/aSales%20Analysis%20Database.xlsm


You just choose the date range and the sales person and charts are dynamically created.


Smallman

 

[Hui]

Maniknandi


I'm not sure here

If I select dates that don't have data the chart shows no data,

If I then select dates that are ok, the chart is good


You could use data validation to select the dates based on the criteria in the Cal sheet


Or have I misread your problem

 

[maniknandi]

Dear Smallman,


This file is not the previous one. This is new file. The problem is if we select the month or year and if that particular month and date is not in our raw data range, chart showing no data, blank. Fine, no problem. But the chart format is changed and again I have to go to the chart design mode then change the chart data range.

 

[Smallman]

Manik


I can see your problem, it is just I had eliminated any possibility of that problem occurring in the file I provided. In my opinion you over complicated what I made simple for you. I would use the technology I built into the file in my previous post to get around this new issues.


Take care


Smalllman

 

[NARAYANK991]

Hi Nandi ,

Try this :

Your formula for cell H11 in the Cal tab is :

=COUNT($E$8:$E$38)

Change this to :

=MAX(10,COUNT($E$8:$E$38))

and see whether it works.

Narayan