Formula that references a cell in a shifting pivot table

[NightCa]

Hello everybody. I'm looking for a way to reference a fixed value in a formula so that when I change the order of the cells (e.g. alphabetically) the value used inside the formula stays the same. Intuitively, I thought setting the formula to absolute reference would solve the problem, but this doesn't work. I know of the formula getpivotdata but it requires you to actually write the value. I'm wondering if there's a way I haven't thought of or if someone could write an UDF to keep track of the selected value (or values) inside the moving pivot table. Here are a couple of pictures to undertand the problem more clearly:

after changing the order of cells inside the table, the selected cell stays A2. instead, i want it to become A8.
Thank you in advance.

 

[Ruddles]

If you can place a comment in the cell you want to track, you could do something like this:-

Option Explicit
Public Function Track(col As String, str As String) As String
  Application.Volatile True
  Dim i As Long
  With ActiveSheet
    For i = 1 To .Cells(.Rows.Count, col).End(xlUp).Row
      If Not .Cells(i, col).Comment Is Nothing Then
        If .Cells(i, col).Comment.Text = str Then
          Track = .Cells(i, col).Address(0, 0)
          Exit Function
        End If
      End If
    Next i
  End With
End Function

Then the formula =Track("A:A","TRACKME") would return the address of the first cell in A:A which contained the comment "TRACK ME", which you could then use in an =INDIRECT() formula if required.

It's 'quick & dirty' and I can't vouch for its performance if you use it in huge worksheets, but it might do the trick.