+ Reply to Thread
Results 1 to 4 of 4

multi-table or multi selection pivot for budgeting

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    sacramento, ca
    MS-Off Ver
    2016
    Posts
    3

    multi-table or multi selection pivot for budgeting

    I'm trying to create a personal budget. I have each payment source on its own sheet, for ease of data entry: Bank debit, Amazon, Visa, etc. And, each of these is organized with the same headers: date, description, amount, and category.

    For ease of budgeting, I need to be able to pull all of these different data sets in to one large set, listing the category, and sum of amounts like the pivot tables on each sheet.

    I've attached a worksheet of how the data is stored with some sample data. And, there's a pivot table on each sheet already in the format I'm looking for. I just can't seem to figure out how to get all of the data from all of the sheets culled in to one pivot table (on a new sheet)--when I do multiple consolidation ranges, I can't get the categories to list at all. I've tried using tables as well with similar results. Surely, this is something easy I'm missing.

    Thanks.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007, 2013, 2016
    Posts
    71

    Re: multi-table or multi selection pivot for budgeting

    I would suggest you to add a column to list out the payment method then create a pivot as a whole.
    Screenshot_1.jpg
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-01-2020
    Location
    sacramento, ca
    MS-Off Ver
    2016
    Posts
    3

    Re: multi-table or multi selection pivot for budgeting

    Thanks. That could work if I could make a master sheet to auto-update references from the individual tabs.

    The reason I have each payment method on its own sheet is I can download .csv from the respective accounts and just copy/paste in to it; then all the data is easy to reconcile with statements rather than jumbled in with other methods. If I have to manually manage them all in to one large chunk of data it just makes more work each time I want to look at an overview.

    I'll fiddle with it for a while longer. In the meantime, if anyone has any other ideas to use the individual sheets, even if I have to add more columns--I'd appreciate the advice.

  4. #4
    Registered User
    Join Date
    03-01-2020
    Location
    sacramento, ca
    MS-Off Ver
    2016
    Posts
    3

    Re: multi-table or multi selection pivot for budgeting

    thought I'd follow up. Not very helpful for excel, but ultimately I was able to do what I was after in google sheets, which is fine for my needs.

    In short, in google sheets you can create an array of multiple sheets' data columns; I never could figure out how to do that in excel. Once this array is compiled I simply created a pivot table off of that data and got what I was after.

    I don't know how to share a google sheet here. But, it's really as simple as starting a new sheet and hitting =SORT({'sheet1!A1:E';'sheet2!A1:E';'sheet3!A1:E'})
    Where E doesn't have a range so it always includes new rows added to the data set sheets. The sort makes sure all the blank cells are put to the bottom of the array.

    Thanks for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-26-2017, 11:48 PM
  2. Writing the loop for updating multi-table MS access file using multi-sheet excel
    By relabz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2016, 03:10 PM
  3. VBA code to run Multi value fields selection in Pivot table
    By Trung in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-17-2014, 12:11 AM
  4. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  5. Replies: 19
    Last Post: 10-05-2012, 01:03 PM
  6. Values on first row of multi row pivot table
    By jorad in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-05-2012, 07:31 AM
  7. Multi-Sheet, Multi-Table Vlookup possible?
    By cte in forum Excel General
    Replies: 6
    Last Post: 09-11-2010, 11:18 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1