Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a value appearing in a pivot table
One of my regular jobs is entering payment statements into a
spreadsheet and then matching up the payment against a bank transaction. The worksheet "Income" has payments info which includes the following info and a dozen other fields not relevant to this question: <InvoiceDate <BankedDate <Payer <Payment The worksheet "Bank account" has transactions downloaded from the bank. A macro of mine translates the narratives into readable english using a dictionary table. It includes the following info among its dozen columns: <BankedDate <Payment <Payer <Reconciled Another worksheet parses downloaded payment statements into my format suitable for the "income" sheet. I have to copy and paste the downloaded statement into a certain range then a series of SUMIFs and vlookups turn the raw payment statement into my format used in "Income". On that same sheet, I have pivot tables which look at the "Bank account" sheet and generate something like the following: BankedDate Yes No Grand Total 21/09/2007 $55.00 $55.00 22/10/2007 $55.01 $55.01 14/11/2007 $58.68 $58.68 19/12/2007 $57.65 $57.65 15/01/2008 $59.11 $59.11 14/02/2008 $54.39 $54.39 13/03/2008 $47.34 $47.34 10/04/2008 $46.89 $46.89 16/05/2008 $46.39 $46.39 16/06/2008 $49.47 $49.47 24/07/2008 $47.30 $47.30 22/08/2008 $44.64 $44.64 22/09/2008 $44.56 $44.56 Grand Total $434.07 $232.36 $666.43 .... where BankedDate is the date the payment was deposited into the account, Yes and No are the two possible values for the <Reconciled field, the numbers are the values of the payments and payer is actually a filter field, so if I select "Fred" for payer, those are all of "Fred's" payments. So, I've just pasted in a payment statement and the value of that statement is $47.30. Referring to the pivot table I can see that a $47.30 payment was made on July 24th, so I copy the value 24/7/2008 into my payments list then hit a macro button which copies that payment to my income sheet. The next job, currently, is to navigate to the bank account sheet, scroll to 24/7/08 and manually change the No to Yes. Next time I visit the statement processing worksheet the pivot table will refresh and look like this: BankedDate Yes No Grand Total 21/09/2007 $55.00 $55.00 22/10/2007 $55.01 $55.01 14/11/2007 $58.68 $58.68 19/12/2007 $57.65 $57.65 15/01/2008 $59.11 $59.11 14/02/2008 $54.39 $54.39 13/03/2008 $47.34 $47.34 10/04/2008 $46.89 $46.89 16/05/2008 $46.39 $46.39 16/06/2008 $49.47 $49.47 24/07/2008 $47.30 $47.30 22/08/2008 $44.64 $44.64 22/09/2008 $44.56 $44.56 Grand Total $481.37 $185.06 $666.43 Thus enabling me to see at a glance which payment statements I need to enter because according to the above I'm missing statements for four different payments. So I need a macro that will enable me to automate the process of finding the <payment payment made on <BankedDate by <Payer and changing <reconciled to "Yes". To make it a little trickier, due to rounding errors the payment is often slightly different to what it said on the statement. Each statement has a number of accounts the money is being spread between and even though I was only paid $47.30, the totals of the payments being copied to the Income sheet might be $47.34 or something. And to complicate things further, sometimes <payer makes more than one payment a day, so I can't merely tell it to go change the 24/7/08 payment from <Payer, it has to specifically change the $47.30 payment's reconciled statement while ignoring other payments made on the same day. Travis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Months not appearing in grouped dates in Pivot table | Excel Worksheet Functions | |||
changing pivot table into a formula table | Excel Programming | |||
Pivot table Row heading appearing in duplicate in Excel 2003 | Excel Discussion (Misc queries) | |||
comment (with little red triangle) appearing in pivot table | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel |