1. 

   jp401
   

   Member
   Joined: Jul '11
   Posts: 3

   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

   Posted 1 year ago #
2. 

   fred
   

   Member
   Joined: Nov '10
   Posts: 516

   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.

   Posted 1 year ago #
3. 

   dan_l
   

   Member
   Joined: Apr '10
   Posts: 520

   ----------
   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?

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics » Excel for Beginners - Tutorials » Excel Formula Examples » Array Formulas » Excel Charts » Pivot Tables » Project Management using Excel » Excel Dashboards » Excel VBA » Advanced Excel

Our Products Full Training Programs » Excel School » Excel Dashboard Classes » Excel VBA Classes » Excel Financial Modeling » Excel for PMs Crash Courses » Excel Formula Course » Excel Dashboards Course Downloads » Excel PM Templates » Excel Formula E-book

About us » About Chandoo.org » Welcome » Best of Chandoo.org » FREE Excel Downloads » Contact Details » Excel Forums