Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Can I use VLOOKUP within pivot tables?

  1. #1
    Join Date
    14th October 2004
    Posts
    1

    Can I use VLOOKUP within pivot tables?

    Hi everybody,
    I'm taking care of a sales reporting system which is based on a main table (10,000+rows X 20 columns) tied to other 3 sheets which refer to it in order to build pivot tables for different "views":
    > pivot table "by customer"
    > pivot table "by item"
    > Pivot table "Overall mix"

    The system is working pretty well, file is manageable (5MB) and quick enough. However, now I'm in the need to add some relations, in particolar I should LINK each item (using the item codification number as the primary key) to a new table cointaining the minimun approved selling price for each item.

    The "simple" solution would be to vlookup this pricing in the main table, but obviously that's no efficient (each price would be repeated each time the item appears in the main table, which could be hundreds). That's why I need to do that in the pivot table. Also, i don't want to add a side-column, external to the pivot table because you can dig the table to expand it and therefore external side columns would be deleted. I am aware that ACCESS would suit much better this application, but there are other reasons why I gotta stick to Excel. Can you maybe help me to understand how can I use a lookup function within a pivot table ?

    Thanks a lot... you all take care
    Marcello.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716
    Hi Marcello

    You can use a Lookup in a Pivot Table just like you would on any other data. However, I would create another Pivot Table based on your raw data and have it show the needed info.

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678
    OK. I found this one quite interesting. So I thought I would give you a completely different (but possibly eye-opening) alternative.

    I have recently been messing with ADO & SQL in order to avoid issues like memory hungry VLOOKUPS & other similar functions, & I thought that your scenario was just perfect for an example.

    The code in the file was developed from Dennis' posts in the Advanced Integration forum so full credit to hime for the inspiration

    To explain, for those who can't download the file.

    Step 1. Set up the Raw data in Two Tables
    I created a table of data in a sheet named TblMajor (sheet name - no need for named range) with fields as follows:
    CustID|ProdID|Period|SalesVolume

    I created a second table in another sheet - the sheet called tblMinor
    ProdID|ProdPrice

    Normally, to get the revenue number of Sales * Price, this is where you would need VLOOKUP or INDEX/MATCH - but this is also where the power of ADO & SQL comes to the fore....

    Step 2. Set up the SQL Query
    I created a separate worksheet called PivotSource, with the following header row in row 1
    CustID|ProdID|Period|SalesVolume|ProdPrice|Revenue

    I then added the following code to a standard worksheet module

    VB:
    Option Explicit 
     
    Sub Create_Pivot_Source() 
         'Credit to XL-Dennis for the original code ideas.
         
        Dim cnt As ADODB.Connection 
        Dim rst As ADODB.Recordset 
        Dim stCon As String, stSQL As String 
        Dim i As Long, lnMode As Long 
         
        Dim wbBook As Workbook 
        Dim wsSheet As Worksheet 
         
        Set wbBook = ThisWorkbook 
        Set wsSheet = wbBook.Worksheets("PivotSource") 
         
         
        stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
        & "Data Source=" & wbBook.FullName & ";" _ 
        & "Extended Properties=""Excel 8.0;HDR=YES"";" 
         
        stSQL = "SELECT [TblMajor$].CustID, [TblMajor$].ProdID, [TblMajor$].Period, [TblMajor$].SalesVolume," 
        stSQL = stSQL & " [TblMinor$].ProdPrice,[TblMinor$].ProdPrice * [TblMajor$].SalesVolume " 
        stSQL = stSQL & " FROM [TblMajor$], [TblMinor$]" 
        stSQL = stSQL & " WHERE [TblMajor$].ProdID = [TblMinor$].ProdID" 
        stSQL = stSQL & " ORDER BY [TblMajor$].CustID ASC" 
         
         
        Set cnt = New ADODB.Connection 
        Set rst = New ADODB.Recordset 
         
        cnt.Open stCon 
        rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic 
         
         'A check to see that records actually exist.
        If Not rst.EOF Then 
            With Application 
                .ScreenUpdating = False 
                 'Collect the present calculation-mode.
                lnMode = .Calculation 
                .Calculation = xlCalculationManual 
                 'Copy the records to the PT Data worksheet.
                wsSheet.Cells(2, 1).CopyFromRecordset rst 
                 'Reset the calculation-mode.
                .Calculation = lnMode 
                .ScreenUpdating = True 
            End With 
        Else 
            MsgBox "No records could be find!", vbCritical 
        End If 
         
         'Cleaning up
        If CBool(rst.State And adStateOpen) Then rst.Close 
        Set rst = Nothing 
        If CBool(cnt.State And adStateOpen) Then cnt.Close 
        Set cnt = Nothing 
    End Sub 
    
    
    The SQL has the effect of multiplying the Sales by the Product Price, just as you would have done with VLOOKUP... but no memory issues & no overheads.

    You need to set a reference in the VBE to MDAC 2.5 or greater to get the ADO to work.

    Step 3. Build your Pivot Table

    So now we have the raw data with all the fields/info we want from the 2 tables. I then added a named range to the PivotSource sheet called PTSource & the refers to box contains the following

    =OFFSET(PivotSource!$A$1,0,0,COUNTA(PivotSource!$A:$A),COUNTA(PivotSource!$1:$1))

    Making the range fully Dynamic

    Step 4. Build your Pivot Table

    Finally I added a sheet called PivotTable, which is where I built the PT

    I merely added the following code to ensure that ensured that the source data is updated each time PT is refreshed

    VB:
    Option Explicit 
     
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
        Call Create_Pivot_Source ' refresh source data via ADO/SQL
        Application.EnableEvents = False 'stop indefinite looping
        With Sheets("PivotTable").PivotTables(1) 
            .RefreshTable ' refresh the PT
        End With 
        Application.EnableEvents = True 'turn back on after refresh event
    End Sub 
    
    
    Hope that this helps.

    Here is the example file for those that might be interested. I will also post this in Advanced forum.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: October 16th, 2005, 01:56
  2. Pivot Tables: Text values in Pivot Tables
    By sergv in forum EXCEL HELP
    Replies: 6
    Last Post: October 7th, 2003, 05:09
  3. Replies: 2
    Last Post: September 11th, 2003, 01:23
  4. Replies: 2
    Last Post: September 6th, 2003, 05:31
  5. Pivot Tables: Recording sales figures in pivot tables
    By loadedplums in forum EXCEL HELP
    Replies: 14
    Last Post: June 20th, 2003, 06:54

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
  •  
porno