Update Main Table with the help of table generated from Pivot Table in Excel

sanits591

Active Member
Joined
May 30, 2010
Messages
253
Hi

I have been searching for a solution for the last few days, but unable to get anything relevant to it.

Data Available ( example)

1. One sheet with Name (Main Data) having the Data (with col A having Unique Values) with Validation in two columns (col D & Col E).
2. A pivot table is generated from this main table, and the values are simply the count and Sum in the "Values" section of the pivot table.
As soon as i double click on the field (on the values) a Data table, say "Generated Sheet" is generated consisting of values, now:

What i am looking for:

a) The column which are having data validation in the "Main Data" sheet should also have the data validation in the same columns (col D & col E) in the "Generated sheet", for the ease of operation or for amending the data.

b) If the data is edited in the "Generated Sheet" then it automatically changes the data in the "Main Data" sheet.
I would like to have this, because, merely editing a small portion on the "Generated sheet" shall be able to update the "Main Data".

I am not interested to touch "Main Data" directly for editing, as "Pivot Table" which is generated having so many filters, and requires to be changed for various analysis and can be drilled down to know the status of "generated sheet", and thereby the modification can be done there itself in "Generated Sheet" and which shall reflect in the "Main Data" sheet also.

I am not clear, moreover, i do not know how to take-off from here for the actions which i am seeking for.

Any assistance shall be appreciated for performing this task.

Thanks!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi santis591,

I have some code that applies number formatting from the source data to the drill-down's data range.

DataValidation (DV) is a little trickier because of limitations on referencing other sheets, but the code I have could probably be adapted to do that.

What type of DV criteria are you using in Columns D & E? (Are you using DV lists or some other type of Validation criteria?)

Do any of your DV criteria point to range references on the same sheet as the source data?

For the second part of your question (changes to the generated sheet update Main data), I think this could be accomplished, but there might need to be some reasonable constraints, otherwise handling all possible scenarios and exceptions might take a good deal of effort.

It's an interesting idea for a tool and I'd be glad to try writing some code to support it. I'll await your response to the questions above.
 

sanits591

Active Member
Joined
May 30, 2010
Messages
253
Thanks for replying, the DV list consists of simply a named range, say "status" taken from another sheet, 'stages'.

Anticipatory thanks.

 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
As a starting point, the code below will copy formats including DV from the top row of data of your datasource to the data rows of the drill-down detail table.

The set up requires copying code into three different code locations in the workbook, each identified below.

Paste into Sheet Code Module of the Worksheet with PivotTable
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
[COLOR="#008080"]'---If user double-clicks in PivotTable data, assigns a string reference to
'---  the Pivot Table's SourceData Property to Public string sSourceDataR1C1[/COLOR]

    On Error GoTo ResetPublicString
    With Target.PivotCell
        If .PivotCellType = xlPivotCellValue And _
            .PivotTable.PivotCache.SourceType = xlDatabase Then
                sSourceDataR1C1 = .PivotTable.SourceData
        End If
    End With
    Exit Sub
ResetPublicString:
    sSourceDataR1C1 = vbNullString
End Sub

Paste into ThisWorkbook Code Module
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject

    On Error Resume Next
    
    Set tblNew = Cells(1).ListObject
    If tblNew Is Nothing Then Exit Sub
    Call Format_PT_Detail(tblNew)
    Set tblNew = Nothing
End Sub

Paste into the top of a Standard Code Module
Code:
Option Explicit

Public sSourceDataR1C1 As String

Public Function Format_PT_Detail(tblNew As ListObject)
[COLOR="#008080"]'---Called by Workbook_NewSheet; Passes ShowDetail table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply Formats in first row of SourceData to tblNew[/COLOR]
    

    Dim sSourceDataA1 As String

    If sSourceDataR1C1 = vbNullString Then Exit Function
    sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
            xlR1C1, xlA1)
    Range(sSourceDataA1).Resize(1).Offset(1).Copy
    With tblNew.Range
        If .Rows.Count > 1 Then
            With .Offset(1).Resize(.Rows.Count - 1)
                .PasteSpecial Paste:=xlPasteValidation
                .PasteSpecial Paste:=xlPasteFormats [COLOR="#008080"] 'optional[/COLOR]
            End With
        End If
    End With
    sSourceDataR1C1 = vbNullString
End Function

Let's make sure this works for you before we explore the idea of linking changes to the Drill-down Table to the Main Data (Pivot's Data Source).
 
Last edited:

sanits591

Active Member
Joined
May 30, 2010
Messages
253

ADVERTISEMENT

Thanks for the effort done JS411, but it is not working with me to generate the DV on the sheet as soon as i double_click on the pivot table. I have done the pasting of code as mentioned above in your code.

Kindly help me to find out where it is going wrong, as no error comes after double_clicking on the pivot table, and the data is generated without any DV.

Thanks!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
To help track down the problem, add a few messages to ensure each procedure is being run.

Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject
[B][COLOR="#0000CD"]    Msgbox "In NewSheet"[/COLOR][/B]
    On Error Resume Next


Code:
Public Function Format_PT_Detail(tblNew As ListObject)
    Dim sSourceDataA1 As String
[B][COLOR="#0000CD"]    Msgbox "In Format_PT_Detail"[/COLOR][/B]
 
Last edited:

sanits591

Active Member
Joined
May 30, 2010
Messages
253

ADVERTISEMENT

This particular code given below does not lead to any message.

Thanks!

Public Function Format_PT_Detail(tblNew As ListObject)
Dim sSourceDataA1 As String
Msgbox "In Format_PT_Detail"

 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Try one more...

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
'---If user double-clicks in PivotTable data, assigns a string reference to
'---  the Pivot Table's SourceData Property to Public string sSourceDataR1C1
   [COLOR="#0000CD"][B] Msgbox "In BeforeDoubleClick"[/B][/COLOR]

Do you know how to use some of the debugging tools in the VB Editor such as Adding Breakpoints and Stepping through each line of code?
 

sanits591

Active Member
Joined
May 30, 2010
Messages
253
The problem which is perceived after introducing this msgbox code for each code, for error checking lies with, which probably is unable to run after double clicking the pivot:

Public Function Format_PT_Detail(tblNew As ListObject)
Dim sSourceDataA1 As String
Msgbox "In Format_PT_Detail"

Thanks!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
The error checking shouldn't stop the message box.

Do you have any Event Procedure Code in your Workbook (besides this code?)

Go to the Immediate Window of the VBE (Ctrl-G if it isn't visible). Enter:
?Application.EnableEvents
<hit enter after typing that statement>

If "False" is returned, that is the cause of the problem. In that case, enter in the Immediate Window:
Application.EnableEvents=True
<hit enter>
Then try double clicking the Pivot again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,495
Messages
5,596,490
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top