Excel '97 + Pivot Table Chart

[kojak43]

I have months of Lottery sales data by market and county.
We are trying to create a chart from a pivot table. We show the sales for each game (Scratch-off, Play 4, Lotto etc) by county as a subset of market.

When we create the chart, it only shows the titles of the counties. The markets are excluded. Is there anyway to include the market titles?

 

[BrianB]

In the absence of other replies :-

It looks like you have not set up your data table correctly. I guess column headings should be something like :-

Market ___County____Scratch-off_____Play 4_____Lotto

 

[kojak43]

Brian, that is how it looks as a pivot table report. When I make a chart, I hide all but one of the games, i.e. lotto, then ask for a chart to be made. It shows the chart for all the counties for the one game. What it does not show are the markets, below the counties. I want the markets to appear on the same axis as the counties.

 

[BrianB]

I use pivot tables a lot and generally find that, however well they do their basic job, the best way of handling the output is to transfer data to another formatted sheet.

A good way of doing this is to use =VLOOKUP() with the range referring to columns without row numbers - because pivot tables data positions can vary. In this case in this case you can set up the data grid for your table and transfer the numbers. eg :-

=VLOOKUP(A1,PivotSheet!$A:$D,2,FALSE)


Hope this helps.

 

[kojak43]

Thanks Brian, I am sure it would help if I was not such a newby.

What I did was copied your code. I inserted a new sheet into the workbook. I placed your formula in A1 of the new worksheet, Changed 'Pivot Table' to 'Sheet7' (that is where the pivot table showing Market, County & Lotto lives) However it is really A-B-H (I have hidden columns c-d-e-f-g as they are different games). I tried it with A:C: as well as A:H. No joy. I get a blue diamond and a 0 in A1 of the new sheet.

Sorry to be so dense.
k

 

[BrianB]

You are not using =VLOOKUP() correctly. A1 in the formula is the where value is that the formula is looking for.

Look up VLOOKUP in Excel help.

 

[kojak43]

I went to Excel's Help. That is going to be a tough road. I do have Mr Excel ON EXCEL and will see if I can figure out how to get vlookup to work.

I went back to the Pivot Table. I changed the size of the paper to 17 x 11 and it worked. My markets are listed below all 67 counties.

In fact I then grouped the dates by month and produced a line chart by market, by county showing a line/sales bullet for each month of Jan, Feb and Mar 2003. It looked great!

I still can't wipe this silly grin off my face.

Thanks for your guidance. I will try to get vlookup to work.

k