Reference changes when filtering table

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!

ohhaykfrankk said:

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.

Click to expand...

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).

ohhaykfrankk said:

If I filter the table on Sheet 1, the numbers on Sheet 2 change

Click to expand...

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.

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.

View attachment Pokemon Go.xlsx

ohhaykfrankk said:

1 change order then the number on sheet 2 changes too :/

Click to expand...

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:

Your should find that sorting Table1 doesn't then chage the values in Table2[[Needed3]]

Thank you!!! It all works perfectly now

Great! thanks for letting me know.