Dear users:

I am trying to create a pivot table (and possibly a pivot chart) that lists
my daily expenses. I have 3 categories for expenses: cash, debit, and
charge. (I don't really write checks--they would get listed as debit). I
also have 3 categories which are a running =SUM of the first 3 categories.
So I have 6 columns total with dollar amounts in them and then I have 2
additional columns that list what type of purchase and sub-purchase category
it is. (The first 3 columns are date, purchase, and location. The next 3
are cash, debit, charge, the next three are the running totals, and the last
2 are the category columns for what type of purchase it was.) For example, if
I bought a newspaper the category would be news from a drop-down list that I
created.

What I want to do is show a table that adds up all the news items, all the
food items, all the office supplies items, etc. How can I do this with the
type of columns and rows that I have? I tried using Pivot Table wizard but I
ended up with data that didn't have dollar amounts. Instead the data had
weird sorted decimals from .5 to 4.5 or higher. Can someone help me create
the right type of Pivot Table? Thanks in advance for your help.
--
-=- penciline -=-

If you organise your data as follows:

Date source Account Value
1/2/06 Cash News 2.50
1/2/06 Debit Food 21.50
1/2/06 Charge Clothes 52.50
1/2/06 Charge News 12.50
1/2/06 Cash Food 31.50
1/2/06 Charge Clothes 102.50

Then a pivot table can easily summarise.
Example
In ROW drag labels of Date and Account
In COLUMN drag label for Source
In DATA drag label for Value

The pivot table will create itself.

Create a second pivot table from the first (Using Source = from
another Pivot Table) if you want an additional view say with Date and
Source as row headings and Account as Column headings.

regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=513485

Thank you very much for your reply and advice.

I am curious now how to create a Pivot Table from multiple sheets. Whenever
I try it fails or doesn't allow me to access the Pivot Table/Pivot Chart
Report menu option. If you or anyone else has any insight on this, please
let me know.

Thanks.


--
-=- penciline -=-


"steven1001" wrote:


If you organise your data as follows:

Date source Account Value
1/2/06 Cash News 2.50
1/2/06 Debit Food 21.50
1/2/06 Charge Clothes 52.50
1/2/06 Charge News 12.50
1/2/06 Cash Food 31.50
1/2/06 Charge Clothes 102.50

Then a pivot table can easily summarise.
Example
In ROW drag labels of Date and Account
In COLUMN drag label for Source
In DATA drag label for Value

The pivot table will create itself.

Create a second pivot table from the first (Using Source = from
another Pivot Table) if you want an additional view say with Date and
Source as row headings and Account as Column headings.

regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=513485