Pulling data into new worksheet from pivot table

[jp401]

Hi - I have a pivot table that contains income statement information. Since pivot tables will not allow for insert of a row with a a formula I want to pull data from the pivot table into a new worksheet to obtain the format I need. Below is format of my pivot table. I have tried to use the get pivot data formula, but if the data changes from month to month (ex. new accounts added or deleted) I can not get the format to remain consistent. I have tried to use a lookup formula based on account name, but sometimes accounts have the same name for different accounts, so it pulls in the first line of information that matches.


If anyone has any ideas or can point me in the right direction that would be great!


Thank you


Account Account Name Company 1 Company 2 Company 3

1010-00 Checking - Acct1 100

Checking - Acct2 150

Checking - Acct3 200

1010-01 Checking - Acct1 200

Checking - Acct2 250

Checking - Acct3 300

 

[fred]

I have a similar situation and here is how I do it. I have a database of staff and they report to a group of managers. So you can see a manager may have anywhere from 1 to multiple staff. I need to create a manager report and as you can imagine managers, like their staff, move in and out of a entity regularly.


As oppose to Account Name which can be confusing I guess when you need to pull data from the pivot table you would use A/C number which is unique, right? So A/C number would be the driving factor.


What I did was adding a column to the left hand side of the pivot table. And assign number starting from 1 to 1000 (or 50000 if you have that many accounts). Then on a helper page (or the result page other than the pivot table page), I would use a combination of "if", "index", and "vlookup" to get what I need.


Whilst reading the sample you have provided, I thought may be you want to clean up the database first so that only account number falls under the "Account" column and account names falls under "Account Name" so that your pivot table would be clean to read.

 

[dan_l]

----------

Since pivot tables will not allow for insert of a row with a a formula I want to pull data from the pivot table into a new worksheet to obtain the format I need.

----------


Well you can insert a calculated field or you can add data to the source and refresh the pivot table?