Pivot Chart???

[gschake]

I'm trying to create a chart showing the number of orders per week fo
each of our customers.

Each row (excluding header) in my Data Worksheet is an order wit
Client Name and Week# that the order was placed in.

I have a pivot table with "Client Name" in the page field and Week
and total (count) displaying correctly. I need the X axis to remai
static for all 52 weeks (with labels) and not adjust and show just th
weeks with data.

I tried creating 2 more columns to chart from. Week (a list wit
values of 1 - 52) and Total. I'm trying to pull data from the pivo
table to fill the Total column for each of the Weeks using OFFSET an
MATCH functions, then with #N/A values returning I started delving int
an IF(ERROR.TYPE) function that reads:

=IF(ERROR.TYPE(OFFSET($A$6,MATCH(20,$A$6:$A$58,0),2))=7,0,OFFSET($A$6,MATCH(20,$A$6:$A$58,0),2))

This seems way to complicated for a simple chart. Is there a bette
solution

 

[gschake]

I figured it out on my own, thanks for everyones help???

Using a Pivot Table, I total my base data into week# and total order
in that week for each client. I added two more columns, Week an
Count. Week (in Column C) is a list of numbers 1 thru 52. I put th
following function under the Count (in column d) which does a VLOOKU
against my Pivot Table:

=IF(ISNUMBER((VLOOKUP(C5,$A$6:$B$58,2,FALSE))),VLOOKUP(C5,$A$6:$B$58,2,FALSE),0)

If there isn't a Week # match in my pivot table, an error comes back.
The IF(ISNUMBER...) changes the error to a value of 0.

When I change the client, the pivot table changes and my two ne
columns change automatically. Using the two new columns,
I created a chart that shows all orders by week including weeks
when there were no orders.

I still think there is a better and easier way to do this... Can't yo
force a range on the X Axis of a graph