Results 1 to 7 of 7

Thread: Reference changes when filtering table

  1. #1
    Acolyte ohhaykfrankk's Avatar
    Join Date
    Jan 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010

    Reference changes when filtering table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey guys, I have two tables on separate sheets, The second sheet references cells on the first sheet. However, if I filter the table on Sheet 1, the numbers on Sheet 2 change because it's reference e.g. cell C7 and not the specific number I need it to follow which is now cell C14..

    Correct:
    Sheet 1:
    B7=Name1 C7=Number1
    B14=Name2 C14=Number2

    Sheet 2:
    A1=C7 (Number1)
    A2=C14 (Number2)

    If I filter sheet 1, this is Incorrect:
    Sheet 1:
    B7=Name2 C7=Number2
    B14=Name1 C14=Number1

    Sheet 2 still:
    A1=Name1 B1=C7 (is now Number2)
    A2=Name2 B2=C14 (is now Number 1)

    I hope I make sense. Can anyone help? Thank you!

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ohhaykfrankk View Post

    Sheet 2:
    A1=C7 (Number1)
    A2=C14 (Number2)

    Sheet 2 still:
    A1=Name1 B1=C7 (is now Number2)
    A2=Name2 B2=C14 (is now Number 1)

    I hope I make sense.
    No it doesn't, Im afraid
    I think that some of the data you supply must be wrong, as you indicate that Sheet2 hasn't changed (except for the numbers) but....

    It starts A1=C7 (Number1); A2=C14 (Number2)
    It ends A1 =Name1 ; A2=Name2

    Is the starting (correct) information for Sheet2 wrong?
    It would help to see your data (before and after filtering).

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ohhaykfrankk View Post
    If I filter the table on Sheet 1, the numbers on Sheet 2 change
    Putting the same categories in Cols A and B on Sheets 1 and 2 and then filtering some names out of Sheet1 I can't replicate the behaviour you describe. Filtering does nothing more than hide the table rows not ticked in the filter boxes.
    As you can see from my attachment, Brian and Paul remain on Rows 3 and 8.
    Attached Files Attached Files

  4. #4
    Acolyte ohhaykfrankk's Avatar
    Join Date
    Jan 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Yeah, your table is what I need to happen but mine doesn't work that way..I have it exactly the same referencing the sheet as ='Sheet 1'!C7 but if the names in column A of sheet 1 change order then the number on sheet 2 changes too :/

    This is the whole thing, there's only 1 number on sheet 2 right now so you'll be able to find it. Don't make fun of me.

    Pokemon Go.xlsx

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ohhaykfrankk View Post
    1 change order then the number on sheet 2 changes too :/
    Sorry - I missed your response on this. The problem is that the formula on Sheet2 remains the same (ie Table1!C7) and it will return whatever is in that cell after the sort. With a table, you can't rely on a simple formula reference being updated correctly when the source data is resorted, as it doesn't say how the records are matched between the tables.

    Try this formula in Table2!J4:
    Code:
    =IFERROR(VLOOKUP([@[FINAL EVO]],Table1[[NAME]:[CANDIES]],2,FALSE),"")
    Your should find that sorting Table1 doesn't then chage the values in Table2[[Needed3]]

  6. #6
    Acolyte ohhaykfrankk's Avatar
    Join Date
    Jan 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Thank you!!! It all works perfectly now

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Great! thanks for letting me know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •