Reference to cells using autofilter problem

Hello,
my problem seems pretty simple. Let's say I have a sheet with 3 columns:
first name, last name and age. I enable the "autofilter" function to decide
if I want to sort by first name or last name. Somewhere on another sheet I
make a reference to someone's age. Let's say that I point to Bill on row 3 so
his age is in cell C3. If on another sheet I make a reference to C3 (for
bill's age) but then I use the autofilters to change the sort... the other
sheet still refers to C3 instead of being updated with Bill's new cell for
his age.

I just realised that when I use the SORT function, it still does the same
thing... the reference doesn't get updated if I resort.. How do I do this??

Am I missing something? I of course want to have the cross-sheet references
updated when resorting!

Autofilter does not actually sort the data, it is just showing you sections
of the data you are asking to see. You should get the results you want if
you actually do a data..sort however unless you are pointing to an absolute
cell address in your second sheet. What does your formula look like in the
2nd sheet? ={sheetname}C3 or ={sheetname}$C$3? (sheetname would be the
actual name of the sheet your guys age was in.)


"Twitos" wrote:

> Hello,
> my problem seems pretty simple. Let's say I have a sheet with 3 columns:
> first name, last name and age. I enable the "autofilter" function to decide
> if I want to sort by first name or last name. Somewhere on another sheet I
> make a reference to someone's age. Let's say that I point to Bill on row 3 so
> his age is in cell C3. If on another sheet I make a reference to C3 (for
> bill's age) but then I use the autofilters to change the sort... the other
> sheet still refers to C3 instead of being updated with Bill's new cell for
> his age.
>
> I just realised that when I use the SORT function, it still does the same
> thing... the reference doesn't get updated if I resort.. How do I do this??
>
> Am I missing something? I of course want to have the cross-sheet references
> updated when resorting!

Well, I did a few tests and the problem is much simpler. I have the same
problem even if I don't do cross-sheet and even if I use data --> sort.
Nothing related to autofilters then.

If I have a cell that makes a reference to C3 (obviously NOT $C$3) and then
so data-- sort... my cell is still pointing to the new data in C3... :|

"tim m" wrote:

> Autofilter does not actually sort the data, it is just showing you sections
> of the data you are asking to see. You should get the results you want if
> you actually do a data..sort however unless you are pointing to an absolute
> cell address in your second sheet. What does your formula look like in the
> 2nd sheet? ={sheetname}C3 or ={sheetname}$C$3? (sheetname would be the
> actual name of the sheet your guys age was in.)
>
>
> "Twitos" wrote:
>
> > Hello,
> > my problem seems pretty simple. Let's say I have a sheet with 3 columns:
> > first name, last name and age. I enable the "autofilter" function to decide
> > if I want to sort by first name or last name. Somewhere on another sheet I
> > make a reference to someone's age. Let's say that I point to Bill on row 3 so
> > his age is in cell C3. If on another sheet I make a reference to C3 (for
> > bill's age) but then I use the autofilters to change the sort... the other
> > sheet still refers to C3 instead of being updated with Bill's new cell for
> > his age.
> >
> > I just realised that when I use the SORT function, it still does the same
> > thing... the reference doesn't get updated if I resort.. How do I do this??
> >
> > Am I missing something? I of course want to have the cross-sheet references
> > updated when resorting!