Share via

pivot table from multiple worksheets in one workbook

Anonymous
2019-09-02T19:20:25+00:00

All, I have been trying to create a pivot table from 4 worksheets, will eventually have more, contained in one workbook. I create a pivot using any of the four and it works just fine, but when I try to add any of the others worksheets I get different data and the amounts are not even close.

the top image is where I used only 1 worksheet, the bottom is the columns on each worksheet, just more data.  Once I open up and of the tables listed on the top image and choose any one of the fields the $amounts will spread evenly across each row, add new columns with increasing amounts for each row, etc.  What I'm wanting is to all each category from each works sheet, even filter category so I can see a up to date total for each row.  I have searched online and have found nothing that works or I'm doing something wrong.  Any help would be much appreciated.  Using office 356 on a windows 10 computer

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

14 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2019-09-02T23:15:46+00:00

    Hi,

    Try this solution - Create a Pivot Table from multiple worksheets in the same workbook.

    Hope this helps.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2019-09-10T02:03:54+00:00

    I think I finally got it to work.  Had to completely delete all I had and start from scratch.  I will see how it goes next month when I again will download 2 more files to combine. 

    thanks again

    Keith

    0 comments
  3. Anonymous
    2019-09-09T23:55:48+00:00

    I just downloaded a new file from the bank, actually 2 of them. Saved both as *.cvs,  I then opened a new excel workbook and imported both files into 1 workbook and I have tried to save as a *.xls as well as the newest version.  On step 2 is says to select all the data on sheet 1 and press ctrl-F3 new and save as "dummy".  Step is to check and see it the name would appear in the name box.  It does not, each time it says A1.  I have tried this several times even continuing on with the steps and still I am not able to get this to work.  I originally had 4 worksheets, after downloading the file again I only had 2, (I changed the date range, to see if that helps).  I'm not sure what the issue is maybe it is the downloaded files. After the files are downloaded they are *.cvs. I can save them as *.xls file or the default is *.xlsx. I have tried both ways and still not getting past step 3.  I have noticed the file contains some blank cells in the ref/check column as well as the memo column.. That doesn't seem to make a difference when I create the table from only 1 sheet.  I have tried the pivot table wizard in excel 365 and single sheet works great, multiple sheets I get all kinds of strange totals.  I don't get hardly any of the categories, unlike single sheet all appears.   I then edited both worksheets and changed all of the blank cells and added "999999" to each cell and got the same results. If you have any more suggestions I would appreciate the input.  In any event I thank all for your help.  This is not a must I just would like to figure it out.

    thanks again

    Keith

    0 comments
  4. Anonymous
    2019-09-06T11:38:10+00:00

    I have not had the time to work through the solution yet.  My plans are to look at this in the next day or so.  I briefly looked at it last night and I think this is the same article I found before reaching out to the community. If so, I could not get it to work properly, so I must be doing something wrong.  My plans are to completely rebuilt the spreadsheet and try again.  I do have one question before  I do this.  I can download some of the information from a financial institution as a *.cvs file.  The file has some blank fields (check number/memo) for example.  Do I need to remove them or place text in the files?  Secondly, will this work if I save the file as *.xlsx.

    thanks so much for your help

    Keith

    0 comments
  5. Anonymous
    2019-09-06T07:10:48+00:00

    Hi Keith,

    Have you referred to the article provided by Ashish? Please let us know if you need any further help.

    Thanks,

    Chitrahaas

    0 comments