Defined Named Cell doesnt work with pivot tables !!

[steve78]

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

ps - pls note as per rules i advise that i have cross posted with microsoft techcommunity. here https://techcommunity.microsoft.com/...es/m-p/3039085
no replies as yet to that one.

[rorya]

The cell does not move when the pivot table refreshes. The data goes into a different cell.

[steve78]

thanks rory
Yes that is a more accurate description of what happens upon refresh.

however i would expect the "defined named cell" which IS given the $A$10, i would expect that to point to the data in the cell.

how would i arrange it so when i click the button it ALWAYS goes to the cell with ALAN1 in it. ??

i was told to give the cell "ALAN1" a defined name, but that doesnt seem to be a solution.

is there any other way, thanks

[DJunqueira]

You could use a formula like this instead of a macro.

Please Login or Register  to view this content.

See exemple in 'I5' inside the attached file.

[steve78]

SOLVED

Hey DJunqueira,

thanks very much, that will do perfectly.

In my original file i will have about 40 links that i need to do. just wondering how i can amend them ( to the correct cell name ), all at the same time. or is it a case of having to edit them one by one.

anyways, thanks you very much DJunqueira, that has worked.

steve r