How can I generate a new worksheet for each cost centre?

How can I generate a new worksheet for each...

Didn't find your answer?

I am using Office XP.

I have a pivot table that breaks my general ledger detail into account#, cost centre, account name, description, the debit amount, and the credit amount.

I have the period# and cost centre# in the Page field, the account name and description in the row field, and the debit and credit amounts in the data field.

The pivot table works fine but I want it to automatically generate new worksheets for each cost centre in the above format. I am not even sure if this is possible (without writing a macro) but I think that I accidentally got Excel to perform this once before.

This is my first post and look forward to hearing from you.

Mark
Mark Heazle

Replies (2)

Please login or register to join the discussion.

avatar
By AnonymousUser
25th Nov 2002 09:23

Use another column
I assume that what you want to end up with is a page for each cost centre and for each period.

The problem's that when you select show pages, Excel forces you to choose only one of your page fields.

A macro would involve a lot of looping through the pivot items, but could be done.

But why not alter your original spreadsheet, and put in an extra column that will concatenate period and cost centre? This combined column could then be used a s the page field in your pivot table?

Just in case you're not aware of it, your can concatenate text by a formula like =A2&" "&"B2.

John

John Beardsworth
----------------------------
Email [email protected]

Thanks (0)
avatar
By neileg
22nd Nov 2002 09:02

The question seems too simple?
I probably don't understand the question, or perhaps it is as straight forward as it appears!

Since you have the cost centre in the page field, then all you have to do is click the Show Pages button in the menu bar and select cost centre. I admit that this will duplicate any existing sheets for existing cost centres, and is not done 'automatically' since you have to click the Show Pages button and delete the duplicate pages.

Of course, this exercise is a piece of cake in a decent database, or even a semi decent one like Access. A simple report in Access will group your data by period and cost centre and accomodate any number of new cost centres, periods, data entries, etc.

Thanks (0)