1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by GarryD, Nov 26, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. GarryD

    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
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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)?
     
  3. GarryD

    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
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    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
     
  5. GarryD

    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.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    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
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  8. GarryD

    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.
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    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 :) :)
     
  10. GarryD

    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 . . . . .
     

    Attached Files:

  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    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
     
  12. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    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
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  14. GarryD

    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
    
     
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Did you read my post yet? I'd recommend the named range for a dynamic data source.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028480

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice