Autofilter and linking to multiple tabs

[bzblewski]

I have a file with multiple tabs. The first tab is a table that is autofiltered. All of the following tabs have multiple cells referring back to the first tab using "=firsttab!A34" etc.

The problem is that when i filter the first tab, all my links on the following tabs change.

How do I make my references stick while filtering? do i have to "F4" every single reference formula? would that even work?

 

[RoryA]

Which version of Excel are you using? A straight cell link should not be affected by a filter (at least it isn't in Excel 2003)

 

[bzblewski]

Version 2003. It's quirky, cause it isn't happening on similar workbooks. is there some option i could have clicked to cause this?

 

[RoryA]

Not that I know of. Do you have any code in the workbook?

 

[bzblewski]

I apologize. my verbage was incorrect. i went back and tried it again. it's only using the "sort" that screws it up. i double checked the filters and that seems to be ok.

There is no code in the workbook.

 

[RoryA]

When you sort, the cell references should not change so if your cell has =sheet1!B2 before the sort, it should still have =Sheet1!B2 after the sort, though the data in that cell may have changed.

 

[bzblewski]

correct, is there a way to get the cell reference to follow the sort? so the reference will actually change along with the sort?

 

[RoryA]

I don't think so. You could use a lookup rather than a straight link?