Posts by Melody00

    Re: Preserving formula reference to pivot table

    Here is the workbook. The dashboard is tab is where the formulas break when the sheets are recreated. Thanks for the help

    Quote from KjBox;790619

    The problem certainly lies with the sheet deletion, that is causing the #Ref in the formulas.

    Can you attach your workbook, impossible to suggest a solution without seeing the sheet(s) that get deleted and how the pivot table references data on those sheets.



    I have a macro which deletes worksheets and then recreates based off the report filter in the pivot table. Anytime I do this all references to the formulas used to gather the data especially the GetPivotData is lost and i have to fix all the #REF! errors manually. Is there a way to prevent this from happening? I used this code from contextures.


    Re: Circular Reference Erro

    Thanks for the reply, the circular cell reference went away when i typed ctrl + shift + enter after typing the formula but now i get #N/A, i cant find any matches at all

    Hello, I am trying to extract unique values from one column to another but i end with a circular reference error. I have data formatted as a table and am using the following formula. The data is in column D and I am trying to get the unique values to show up in column J. appreciate any help. I have the formula starting column J2

    Filed Against Column J

    index(Table1[@[Filed Against], match(0,countif($j$2:j2,Table1[@[Filed Against])



    I am using this code to scroll to the top of the sheet when switching worksheets but its not working, would appreciate any help.


    Have a button on Sheet1 which onclick takes me to sheet 2, row 20
    when i go back to sheet1 or any other sheet i want sheet 2 to scroll up all the way A1.
    I have the code on Sheet2 which is not working

    Private Sub Worksheet_Selectionchane(ByVal Target As Range)
        Application.Goto Reference = Range("A1"), Scroll = True