Solved: Excel Pivot Table (based on query with formula)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry
 
Joined
Jul 25, 2004
Messages
5,458
Hi there, welcome to the board!

If you had 2007 or beyond this would all be done automatically for you by utilizing Tables.

If you don't have plans on upgrading, can you tell us the ranges you're working with? What sheet/columns/rows/cells are your data in? What is the formula(s) you're wanting copied down? What is the name of the PivotTable you want updated, and what worksheet is it on?

Also, what do you want to fire these off? When the workbook opens? When you select a particular worksheet? When you manually fire it off (i.e. click a button)?
 

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
I have access to all versions of Excel from 2000 to 2010 but the client in this case is still using 2003 and is showing no signs of upgrading any time soon so we work with what we have :)

File name is SalesByProdGroup.xls

The query is returned to a sheet called Invoices

Data range in Invoices is currently A1:W8807 including the title row. Last time I checked a refresh would increase that to A1:W9064.

The formulated columns are V and W

Formula in V2 is =IF(E2=1,P2*-1,P2)

Formula in W2 is =IF(E2=1,Q2*-1,Q2)

Nothing too complex. They look for the value in Col E and if it equals '1' (which designates the transaction as a credit note) it reverses the sign of the value in Col P or Q respectively.

The pivot table is called PivotTable2 on a sheet called SalesByProductGroupPivot

I would prefer to have a button to run the code when required instead of having it run when the file is opened.

Please let me know if you need any more information.

Thanks

Garry
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
i wonder if you could just add a formula to the pivot table , i have done this before - it may work been a while since i have done that though - the formula should create the ability to add the extra fields and work on a refresh
this is in 2003

can you add some dummy data in a spreadsheet with the pivot table and perhaps i can look, and add the formula - this maybe what Zack Barresse is proposing but sounds like from the questions maybe a VBA solution

you may want to look at that as an option yourself
once you have the pivot table created
goto the pivot table tool bar
click on the pivot table dropdown arrow - choose
formula
calculated field
 

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
I'll give that a try tomorrow and let you know if it works. I need to log on to the client's server to access the data and I've decided I've had enough Excel for the day.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
if you dont get it to work and if you post some dummy data, i would be happy to have ago - just a few rows of data would do - just to test the formula
also on UK time zone
 
Joined
Jul 25, 2004
Messages
5,458
Yes, sounds like a Calculated Field would work nicely. I'm not sure how the PivotTable is setup though.

And you could use a named range as the data source for the pivot table, so it will always have all of the info. Then to update you'd only need to do it manually. I would also have it set to update/refresh on file open.
 

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
I replicated the formula in Col V (=IF(En=1,Pn*-1,Pn) with a calculated field in the pivot table as follows:

=If(DocumentTypeID=1, InvoiceCreditQuantity*-1, InvoiceCreditQuantity)

The calculated field includes the correct records, but the total is incorrect.

For example, one line of my original pivot table based on the formulated cells appended to the returned query results includes 3 records with the following value in Invoices sheet Cols E, P and V respectively (sorry, can't figure out how to tab the results):

0,1,1
0,1,1
1,3,-3

Net total of should be -1, which it is in my original version, but the calculated field total based on the above formula is -5 so it appears to be taking the integer value of the InvoiceCredityQuantity field (Col P) of the three records and reversing the sign of all of them instead of just the one record where DocumentTypeID=1.

Any suggestions for where I'm going wrong?

I realise it's difficult to debug when looking at an abstract and I will happily post an example of the actual data, but where/how?

Next step, named ranges, but breakfast is calling :cool:

Just tried a named range including the entire data area in Invoices A1:W8800 but it does not expand when I refresh the data and the rows increase to W9064.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
(sorry, can't figure out how to tab the results)
use the word code in square brackets , ignore the apostrophes '[ 'code '] (otherwise it uses the code tag) and then at the end use /code in square brackets '[ '/code ']

Code:
1   2    3    4
to upload an xls file - with dummy data - remember this is a public forum, so the spreadsheet is available to all
To upload it to the forum, open the full reply window and use the Manage Attachments button to upload it here.

just had my breakfast :) :)
 

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
Here's an example (125 records) of the data with any commercially sensitive info obscured.

I've only included transactions for months 1 and 11. Majority are invoices (DocumentTypeID=0) but there are a few credit notes (DocumentTypeID=1).

The data in the Invoices sheet has been converted to values with the exception of Cols V and W which still retain their formulae.

The data range in Invoices is A1:W125 but the named ranged automatically created by Excel (GarrySalesbyProdGroup) is A1:U125 i.e. it excludes the formulated columns V and W.

The pivot table totals currently agree to the sum totals in Cols V and W. Obviously if you change the selection criteria in the pivot table the totals will change.

Must be time for lunch now . . . . .
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
i see what you mean - I just added the formula and for MISC I got -2 instead of 0 i guess - 2 rows - row1 should = -1 and row2 =1 total -0

just another idea - if you set the data up as a list where the query populated - that should preserve the formula you have and copy down - never tried with a query - only copy and pasting a report into a list - that worked OK and kept and copied the formula

i'll play a little more before F1 on and probably during if a procession
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,486
it would appear the calc formula only works on the group and not the single line entries - the calc item is for single line but cant get that to work on an IF
so you may need a VBA process or see if the list will still operate and allow the query to still come into the sheet
tools

Data> List > create the list on the sheet and include the formula

i have a feeling the query will not populate into a list
 
Joined
Jul 25, 2004
Messages
5,458
To keep your data source dynamic, just create a defined name. You could use this, titled 'Data', then change your PivotTable data source to 'Data'...
Code:
=OFFSET(Invoices!$A$1,0,0,COUNTA(Invoices!$A:$A),23)
I'm not exactly sure why the calculated field is coming out that way. When drilled down, the data looks normal. Probably best to keep the columns in your data source (and the above named range goes to column W to include them) and use them normally in the PT.

That fixes the problem with the data source being dynamic, but it won't automatically copy your formulas down. This code will do so (and update the PivotTable data source as well)...
Code:
Sub FillFormulas()

    Dim WS As Worksheet
    Dim wsPT As Worksheet
    Dim PT As PivotTable
    Dim iLastRow As Long
    
    Const sQtyCol As String = "V"
    Const sValCol As String = "W"
    
    Set WS = ThisWorkbook.Worksheets("Invoices")
    Set wsPT = ThisWorkbook.Worksheets("SalesbyProductGroupPivot")
    Set PT = wsPT.PivotTables(1)
    Let iLastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
    
    
    '/// If you want to clear the range first, i.e. the data may
    '    be shortened, use this line of code...
    WS.Range(sQtyCol & "2:" & sValCol & WS.Rows.Count).Clear
    
    WS.Range(sQtyCol & "2:" & sQtyCol & iLastRow).Formula = "=IF(E2=1,P2*-1,P2)"
    WS.Range(sValCol & "2:" & sValCol & iLastRow).Formula = "=IF(E2=1,Q2*-1,Q2)"
    
    '/// Use this line of code to automatically update the PivotTable
    PT.RefreshTable
    
End Sub
HTH
 

GarryD

Thread Starter
Joined
Nov 6, 2007
Messages
57
Thanks for the suggestions. I will try them out tomorrow.

In the meantime I adapted the following inelegant but effective code to copy and paste the formula in Cols E and F after refreshing the data and then resize the pivot table data area. The sheet names and cell references do not refer to the file I uploaded earlier but to a simpler test file with the 6 columns of data in Sheet 1 and the pivot table in Sheet 4.

I haven't coded anything for a couple of years and I seem to have forgotten more than I realised but I still remember how to beg, steal or borrow :cool:

Code:
Dim RangeRows As Variant
Dim RangeCols As Variant
Dim newAddress As Variant

' Need code to refresh query data

RangeRows = Application.CountA(Range("A:A"))
RangeCols = Application.CountA(Range("1:1"))

Sheets("Sheet1").Select
Range("E2:F2").Select
    Selection.Copy
    ActiveSheet.Range(Cells(3, RangeCols - 1), Cells(RangeRows, RangeCols)).Select
    ActiveSheet.Paste
    Range("a1:a1").Select
    
' Code to expand pivot table range after data refresh

Sheets("Sheet4").Select
    newAddress = Sheets("Sheet1").[A6].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    Range("a5:a5").Select
    Sheets("Sheet4").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "Sheet1!" & newAddress
    Sheets("Sheet4").PivotTables(1).RefreshTable
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top