Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-02-2015, 05:23 PM
ThegoLopez ThegoLopez is offline Windows 8 Office 2013
Novice
 
Join Date: Jan 2015
Posts: 1
ThegoLopez is on a distinguished road
Default I can't figure out getpivotdata for my monthly budget

OK, So I've spent nearly the entire day looking through countless forums to see if anyone has had this question answered and could not find anything on this. Hopefully someone here can help.

I have a monthly budget. I input my purchases into a sheet called "Checking Ledger", then pivot table data is organized in another worksheet called "Checking Totals", and then the data is grouped by category and added together on the main page. So it kinda looks like this. So all purchases made at Safeway, Whole Foods, & GG Market are put under "Groceries" is added up and the total updates in the "Food" Section on the main worksheet. It looks something like this:

-Groceries



Whole Foods $5.75
12/3/2014 $2.75
12/10/2014 $3.00

Safeway $20.00
12/10/2014 $10.00
12/30/2014 $10.00

The formula I've been using is:

=GETPIVOTDATA("Sum of Payments",'RCU Checking Totals'!$A$3,"Expense Type","Groceries")

That has worked perfectly. BUT, every month I have to erase everything in the ledger for the next month. This makes me have a separate workbook for January, February, March, and so on. The reason I do this is because I'm trying to get my finances in order and want to see how much I spend on a particular thing in a particular month. That way I can predict how much I'll be spending in the future.

What I would like to do is have one workbook for the year, and change the formulas so that the main page will only get the items for a particular month. I can't seem to figure it out. Can anyone help me?
Reply With Quote
  #2  
Old 01-05-2015, 02:21 PM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

How about keeping all the data in one sheet (be a lot easier) and filter the pivot by the dates you want? I think that can be easily done though admittedly I don't use pivot tables much. Let me see if I can work something up for us to look at.
Reply With Quote
  #3  
Old 01-05-2015, 02:59 PM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

See how this looks for you. I've grouped the data by month and year. To do that, you right click on any of the date values (so for here, you would right click either Jan, Feb, or Mar in row 3) in the pivot table and select Group. You can also filter by a specific month.
Attached Files
File Type: xlsx BudgetPivot.xlsx (16.6 KB, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
budget reports in MS Project Professional 2013 shuttlebust Project 1 11-17-2014 05:42 AM
Budget cost on early stages Baddim Project 9 10-30-2014 03:06 AM
Caption Order: Figure 4 Figure 3 Figure 2 golfarchitect13 Word 5 05-07-2014 07:15 PM
How to apply budget for earned value analysis ttorneby Project 2 06-03-2010 04:52 PM
Income/Budget Calendar? Guinea Excel 4 03-11-2010 09:11 PM


All times are GMT -7. The time now is 07:31 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft