PLLLLLLSSSSSS HELP

[PPJ]

Hi

I am pulling data from Sheet 2 (monthly sales data) to Sheet 1 using
Vlookup.

Example of headings in my Sheet 1.
SalesExecutivesName SalesAug SalesSept SalesDrop AugSales DropSept

I pull this data based on the drop down that I have set for Managers,
IF I click (Form Combo box used) one of the managers name in the drop
down (Form-Combo box) his associated Sales Executive names with the
data appears accurately.

Now, assume that Manager 1 has 10 sales executives and Manager 2 has 20
sales executives. When I select Manager 2 it is fine, but when I select
Manager 1 it shows data in 10 rows and the remaining 10 rows are filled
with #N/A, just to overcome this I have used ISERROR() so where there
is error fill it with "" to give a neat look to the sheet

The problem is, I represent the above data in a Graph (Line with 2
Axes) , X axes shows zeros plotted for all the rows where the ISerror()
has filled with "" now i used excel help and understood if I return
NA() then it would hide these zeros, Yes it is hiding zeros in the X
axis, but in the graph area it shows #N/A,

Apart from this I have ensured that the option Tools -> options - >
Chart is set to Not Plotted Leave Gaps.

My question is Is there is possible solution to this Macro / any stuff
which will help me overcome this issue.

My Guess to resolve this issue is to hide all the rows which displays
#N/A so that graph plots only the visible cells, but again i have to
manually unhide. Is there a way as I change the drop down in the combo
a macro unhiding / hiding the rows which display #n/A

or any possible solution ?

Pls suggest I have wasted about two days exploring this stuff, have no
clue. Iam desperate to resolve this.

Thanks
Joseph

 

[Jim Thomlinson]

How about using a pivot table... Place the cursor in the data area and select
data -> pivot table. You should be able to just select finish as it should
make all of the coprrect assumptions for you. Drag the managers to the filter
at the top and place your executives in the right hand column, amounts in the
middle and you are off to the races. You can then just create a pivot chart
off of the pivot table...