To Apply formula in Pivot column

Alka Bajaj · Dec 21st 2011

[HR][/HR]Hello Expert,

Requirement: I have a workbook with many pivots many it. Each of the pivots has a field “Request No”. This field has an embedded hyper link in the source data sheet, which when clicked direct us to source.

But when I pivot this data, hyperlink is lost. I need to activate this hyperlink in pivot so can think of following code

Need you help to guide me on how to build up a macro code for following steps.

For each pivot in the workbook,
Look for Pivot field= “Request No”
For All Request No in that column
Apply formula =HYPERLINK(CONCATENATE("http://cormel- itg01:8080/itg/web/knta/crt/RequestDetail.jsp?REQUEST_ID=",Request No),Request No)

This would help me to replace the entire request No with the hyperlink for all the pivots in the workbook.

Appreciate your support on above.

Many thanks !

Cheers,
Alka

**rory** · Dec 21st 2011

Re: To Apply formula in Pivot column

You can't enter a formula for an item name in a pivot table.

Alka Bajaj · Dec 21st 2011

Re: To Apply formula in Pivot column

Ok. How in this case I can take out the request No from the Piviot row label, and add in as a last column in the pivot
"Request No" and apply following formula to retrieve the data along with the hyperlink.
=HYPERLINK(CONCATENATE("[URL="http://cormel-itg01:8080/itg/web/knta/crt/RequestDetail.jsp?REQUEST_ID=",VLOOKUP(F9,'Issue"]http://cormel-itg01:8080/itg/w…estDetail.jsp?REQUEST_ID=",VLOOKUP(F9,'Issue[/URL] Query Risk'!F:N,9,FALSE)),VLOOKUP(F9,'Issue Query Risk'!F:N,9,FALSE))

Hence to summarize ,can we automate below,
to traverse through all the pivots of all the sheets in the workbook and add the additional column at the end of the pivot with the above formula.

Steps Required:
For each pivot in the workbook,
Add Request No as the last column
Apply formula =as mentioned above,

Regards,
Alka

**rory** · Dec 21st 2011

Re: To Apply formula in Pivot column

What is F9 supposed to be?

Alka Bajaj · Dec 22nd 2011

Re: To Apply formula in Pivot column

F9 is look up value, just a reference to a cell for vlookup.

**rory** · Dec 22nd 2011

Re: To Apply formula in Pivot column

I know that, but it's a relative reference, so is every formula added next to a pivot table supposed to refer to cell F9 specifically, or is it supposed to be relative to where the formula is added?

Alka Bajaj · Dec 22nd 2011

Re: To Apply formula in Pivot column

My bad.It would be different for different pivots. Formula has to be relative depending on pivot & data location.
In this case we need to Lookup_value in reference to the column " SHort Description"

All pivots would have this column as their row data.

Thanks again for all the help!

**rory** · Dec 22nd 2011

Re: To Apply formula in Pivot column

Can you post a sample workbook for testing and so that I can see the layout you have? Depending on the pivot setup, a simple formula may work, or the code may need to find and store the lookup values.

Alka Bajaj · Dec 27th 2011

Re: To Apply formula in Pivot column

Hi Rory,

I am unable to attach the file to this forum, Please let me know if I can mail you.

Appreciate your support on this. Really required.

Thanks.

Regards,
Alka

AAE · Dec 27th 2011

Re: To Apply formula in Pivot column

Why can't you attach a file? If it is due to size, try using ZIP compression or a an online service.

The workbook should contain fake data, but exactly duplicate the structure and data types and contain only enough data to illustrate the problem.

[COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]

1. Click the EDIT POST in the gray band immediately below your post
2. Click Go Advanced
3. Edit the post or thread title
4. To upload: scroll down to Manage Attachments

Alka Bajaj · Dec 28th 2011

Re: To Apply formula in Pivot column

Hi All,

Please refer the attached.

Reporting Dashboard_Sample.xlsx

Regards,
Alka

**rory** · Dec 28th 2011

Re: To Apply formula in Pivot column

Try this:

Code

Option Explicit




Const mc_strHYPERLINK_BASE As String = "http://cormel-itg01:8080/itg/web/knta/crt/RequestDetail.jsp?REQUEST_ID="
Sub AddFormulasToPivots()
   Dim PT                As Excel.PivotTable
   Dim wks               As Excel.Worksheet




   Application.ScreenUpdating = False
   For Each wks In ThisWorkbook.Worksheets
      For Each PT In wks.PivotTables
         ProcessPivot PT
      Next PT
   Next wks
   Application.ScreenUpdating = True
End Sub




Sub ProcessPivot(pvt As Excel.PivotTable)
   Dim rngPT             As Excel.Range
   Dim rngFormulas       As Excel.Range
   Dim rngCell           As Excel.Range
   Dim PF                As Excel.PivotField
   Dim lngCol            As Long




   On Error Resume Next
   Set PF = pvt.RowFields("Short Description")
   On Error GoTo 0




   If Not PF Is Nothing Then
      lngCol = PF.LabelRange.Column
      ' get pivot table data range
      Set rngPT = pvt.DataBodyRange
      Set rngFormulas = rngPT.Offset(, rngPT.Columns.Count).Resize(, 1)
      rngFormulas.FormulaR1C1 = "=IF(RC" & lngCol & "="""","""",HYPERLINK(CONCATENATE(""" & mc_strHYPERLINK_BASE & _
                                """,VLOOKUP(RC" & lngCol & ",'Issue Query Risk'!C6:C14,9,FALSE)),VLOOKUP(RC" & lngCol & _
                                ",'Issue Query Risk'!C6:C14,9,FALSE)))"
      ' clean up any blank cells
      For Each rngCell In rngFormulas
         If rngCell.Text = "" Then rngCell.ClearContents
      Next rngCell
   End If
End Sub

Display More

Herbds7 · Dec 28th 2011

Re: To Apply formula in Pivot column

Excel 2010 Tables, PivotTable
Add HyperLinks within PT.
Macro operates on HyperLink string.
Single PT.
Links within same workbook.
http://c3017412.r12.cf0.rackcdn.com/12_27_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

Alka Bajaj · Dec 29th 2011

Re: To Apply formula in Pivot column

Thanks Rory for the VB. It works as expected.

However, appreciate if you let me know how to resolve below

1. Inserted column, requires few formating as highlighted in attached.
2. Also, pivots will be usually filtered based on certain fields. When I run this macro, only the displayed rows fetchs the request no. In an event, when someone unfilter the field, then the request no is not displayed correctly, until n unless the macro is re-run.

Thanks again for the help.

Regards,
ALKA

**rory** · Dec 29th 2011

Re: To Apply formula in Pivot column

Re #2, you'll have to run the code again - using the worksheet's pivottableupdate event is probably easiest.I'll look at the formatting when I'm at a computer.

**rory** · Dec 29th 2011

Re: To Apply formula in Pivot column

Change the ProcessPivot routine to:

Code

Sub ProcessPivot(pvt As Excel.PivotTable)
   Dim rngPT             As Excel.Range
   Dim rngFormulas       As Excel.Range
   Dim rngCell           As Excel.Range
   Dim PF                As Excel.PivotField
   Dim lngCol            As Long








   On Error Resume Next
   Set PF = pvt.RowFields("Short Description")
   On Error GoTo 0








   If Not PF Is Nothing Then
      lngCol = PF.LabelRange.Column
      ' get pivot table data range
      Set rngPT = pvt.DataBodyRange
      Set rngFormulas = rngPT.Offset(, rngPT.Columns.Count).Resize(, 1)
      With rngFormulas
         .FormulaR1C1 = "=IF(RC" & lngCol & "="""","""",HYPERLINK(CONCATENATE(""" & mc_strHYPERLINK_BASE & _
                        """,VLOOKUP(RC" & lngCol & ",'Issue Query Risk'!C6:C14,9,FALSE)),VLOOKUP(RC" & lngCol & _
                        ",'Issue Query Risk'!C6:C14,9,FALSE)))"
         .Offset(, -1).Copy
         .PasteSpecial xlPasteFormats
         .Offset(-1, -1).Resize(1).Copy .Offset(-1).Resize(1)
         .Offset(-1).Resize(1).Value = "Request No"
         .Resize(.Rows.Count - 1).Style = "Hyperlink"
         With .Cells(.Count)
            .FormulaR1C1 = "=COUNT(R" & rngFormulas.Row & "C:R[-1]C)"
            .HorizontalAlignment = xlHAlignRight
         End With
      End With
      ' clean up any blank cells
      For Each rngCell In rngFormulas
         With rngCell
            If .Text = "" Then .ClearContents
         End With
      Next rngCell




   End If
End Sub

Display More

and then in the ThisWorkbook module, add this:

Code

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
   ProcessPivot Target
End Sub

Alka Bajaj · Dec 29th 2011

Re: To Apply formula in Pivot column

Thanks Rory.

But I am getting an error at
.Resize(.Rows.Count - 1).Style = "Hyperlink".

Also, wrt # 2. I need to run the code again everytime? What is worksheet's pivottableupdate event ? Is it a normal pivot refresh?

**rory** · Dec 29th 2011

Re: To Apply formula in Pivot column

What error are you getting?

Alka Bajaj · Dec 29th 2011

Re: To Apply formula in Pivot column

Code breaks at the line. Cannot execute further.

**rory** · Dec 29th 2011

Re: To Apply formula in Pivot column

I mean, what is the error message? The code works fine for me (see attached version of your file).

To Apply formula in Pivot column

Participate now!

Share