Can I update data source from pivot table?

Hello there,

I would like to know if its possible to edit the source of the data by updating the content of a pivot table; this, when the field of the pivot table I'm trying to update is a row and not a value.

My goal is to correct typos that are present in multiple rows, without having to update the data source as it would be more difficult to do.

Regards

Are you trying to do via VBA?

Hello,

I would tend to believe it is the only way, as I've been trying with the Pivot tool itself and this is not a native function; but if you know that this can be achieved without VBA it would be fine. I'm posting it here since there doesn't seem to be another way.

In any case thank you very much for your time, @JapanDave

edit: I edited my original post in order to update the goal I want to achieve

Can you post a sample file with a little explanation in it what and where you need done?

Hi there, thanks for your response.

I have uploaded the files here: http://1drv.ms/22xQggK

In the column called "Institución (solo educativas)" inside sheet "cs_col" is the name of several academic institutions.
If you create a pivot table as follows:
5idSRvk.png

You will see that it gives you all the names of the institutions, they are all the same for all the files. Now, the main problem is that they might have several typos or simply they are not standardised through all files, one lowercase can make the difference.

I have a list of the standardised institutions, all of them. What I need is to make sure that the institutions in the files have such standardised name.

Any advise?
Thank you,
Regards

I could do something with that file, but it would be better if you made the pivot table the way you need it and then tell me what part of the table you want to change along with the source data.

Hello,

Inside file WoS - 2005 I've created a sheet called "Sheet1" with the pivot table. The values I used are random, I just need to generate the list of the institutions shown in the first column, which are those in the column called "Institución (solo educativas)" in sheet "cs_col"

I hope you understand the nature of my problem; I had said Pivot Table and Editing the Source content from there simply because I found that would be a suitable solution for me. In any case, The pivot table does give me the list of all institutions in that current file; if I was able to use that table to edit the source content, it would be easier to correct the typos present in all other files as well.

Thanks for your time!

Sorry, been busy,

Try this. Put the code in the WorkSheet module on sheet1. Then double click what institution name you want to change in sheet1 column A.
A message will pop up and input the correct spelling there and click ok. The source data will update and so will the pivot chart.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim ws As Worksheet
  Dim r As Range
  Dim x As String
  Dim pt
  
    Application.ScreenUpdating = 0
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
      Set ws = Sheets("cs_col")
      Set r = ws.Columns(6).Find(What:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
       x = InputBox("Enter the correct spelling", , "")
        Do While Not r Is Nothing
            r = x
            Set r = ws.Columns(6).FindNext(After:=r)           
        Loop
            For Each pt In ActiveSheet.PivotTables
                pt.RefreshTable
            Next pt
     End If
     Application.ScreenUpdating = 1
End Sub