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.


    [sw]*[/sw]



    Here

    Files

    • test.xlsm

      (45.03 kB, downloaded 22 times, last: )

    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.


    TIA


    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
    AA
    B
    C
    AA
    S
    C


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


    TIA

    Hello,


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


    Scenario


    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


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