Defined Named Cell doesnt work with pivot tables !!

[stever78]

Hi,

Appreciate your help with something that is puzzling and now annoying that i cant seem to find a solution.

i have a large pivot table with 10,000 rows in and so when i want to find an item i had created a button (which just has a link to a defined name cell).

however , when the pivot table updates and adds more rows the defined name cell DOES NOT move with the table.

( this is strange to me as the defined named cell uses the $row$ signs then i would expect the link to move with the table when it updates )

(( also strange is that it DOES move with the cell if using defined names outside of a pivot table !!))

anyway, i have attached a file which is just a snippet of my document due to data protection, it shows the link at the top called ALAN1. this is linking to defined named cell called ALAN1 ($A$35). 

i updated my pivot table and ALAN1 the actual cell has moved to A37 but the link HAS NOT MOVED with it.

so it seems that using defined named cells doesnt work with pivot tables ( unless anyone know how to fix this )

Does anyone know how i can get a link button ( or any other type of link ) that works dynamically in a pivot table ( by that i mean when you update the table and the cells move the link still points to the correct place. (i.e moves with it)

Please do reply if anything doesn't make sense or you have any questions, your help is gratefully received and appreciated.

basically i need to be able to click a button or link and it takes me to that cell in the pivot table every time ( like you would do if going cntr+f (find) and searching the cell name ).

many many thanks, steve r

[#2]

@stever78 

Do i need to use =GETPIVOTDATA, when linking to data inside of pivot tables.
i have tried this just now put i don't know how to use this to reference a particular call

[#3]

@stever78 

Since you work with data model I'd add measure like

Count Yes or No:=COUNTA( Table15[Yes or No] )

and use

=CUBEVALUE(
  "ThisWorkbookDataModel",
  "[Table15].[T col].[,exch no ind]",
  "[Table15].[Yes or No].[loss]",
  "[Measures].[Count Yes or No]")

if with parameters it will be like

=CUBEVALUE(
  "ThisWorkbookDataModel",
  "[Table15].[T col].[" & $M$2 & "]",
  "[Table15].[Yes or No].[" & $M$3 & "]",
  "[Measures].[Count Yes or No]")

[#4]

im sorry sergie, i dont follow.
i am trying to create a link that links text to a cell in the pivot table.
i dont know what the above does or where to put it, does it create a link ??

[#5]

@stever78 

I guess actually you don't need a link on abstract cell in grid within PivotTable, you need to return value for the combination of some fields.

Above formula returns Count of Yes or No for Yes or No = "loss" and T col = ",exch no ind". Actually it returns value in the intersection of above.

That's virtual link which uses PivotTable coordinates, not grid references.

Alternatively you may use GETPIVOTDATA(), it will be like

=GETPIVOTDATA("Level day",$A$9,"Yes or No","loss","T col",",exch no ind","Special","lev","POST","b2b")