Blogger
Share this content
0
3
7490

Can't get my pivot table to produce two Grand Totals

I am trying to audit the stock provision of a client company and have extracted from Sage (and for fields marked * calculated) the following

Code
Description
Number in stock
Unit value
Stock value
Age (in years)*
Provision % *
Provision * (Provision % x Value)

I have used a pivot table to analyse this and it works fine - I want to use Age as a page field and have set it up with everything as rows except sum of Provision which is data

Ideally I want a total of Stock Value and of the Provision, but if I move anything into the data area Excel says I have to move a field to Page, Row or Column and there is no grand total for a row field.

I am using Excel 2003

I know I am missing something obvious - but what is it?

Chris Lloyd

Replies

Please login or register to join the discussion.

avatar
By Anonymous
26th Jul 2006 16:38

Use Sage ODBC driver into Access ..
Sage L50.v.8 used to have ODBC driver which exposed some of the data (inc the Stock table) - first install the driver

With this in mind either Excel or Access can be used - personally favour Access but that is choice, and down to the correct tools for the job (Access handles large volumes of data better than Excel)

If Access is chosen the
File-->GetExternalData-->Link & grab all the tables

The advantage with linking is that if you ever change the underlying client DB then anything you have set up in Access (all queries) simply applies to the new client

Then either use the CrossTab Query Wizard or build the CrossTab Query manually

Thanks (0)
avatar
27th Jul 2006 16:33

looks simple enough
Chris, hi
As I understand it, you want to see the Data area containing two fields - "Sum of Stock Value" and "Sum of Provision".

To my knowledge you can have as many fields as you like in the Data area, so this shouldn't be a problem.

What exactly is this message in which "Excel says I have to move a field to Page, Row or Column.....". When exactly does it occur?

Thanks (0)
avatar
26th Jul 2006 15:48

Please clarify
Hi Chris

It looks as though you are trying to 'pull' too much information in one go for Excel.

I don't use Sage so I don't know whether you can get contents of file fields directly using ODBC. This would preclude the necessity to pull the item description with the pivot.

If you can't do that, I would have simply the Item Code, Qty., and total value in one sheet. Have the code as the row and the other two as data. You can split the two columns by selecting layout 9 from the auto layout menu.

In another sheet pull the item code and description; then use '=VLOOKUP' to match the descriptions to the other sheet, OUTSIDE the pivot. I always add columns to the right of pivot tables and read all the data into them, taking care to actually TYPE the cell refs. DON'T use the arrow keys as strange things can happen, one being that it places absolute refs. which, when copied, don't work down the page.! Using the duplicated data is far easier than trying to fight the pivot table.

If you don't get a better suggestion, let me know how you get on.

p.s. I do this with 20,000+ items but using an SQL database, which makes life easier!

p.p.s. - Sorry, if it's not obvious, all your other columns can be done by calculation.

Thanks (0)