sort data without formulas in other cells changing?

Hi,

Can someone please help? I've got quite a lot of data that I want to sort
by the persons date of birth, but because I have cells with formula in it
(this works out the persons age) the sort function is changing the formulas
so the formulas no longer work becuase it changes the cell where it is
getting the data from. Does anyone have any ideas how this could be fixed??

Thanks!

Just select the part you want to sort and then sort. This should not change the references in your formula.

Still, if you can give a clear example as to what exactly is happenning, you will get better help.

- Mangesh

I bet you have formulas that point directly at other cells in another worksheet:

=sheet2!b3

If you do, you may want to see if you can redesign your worksheet so that you
can use some key column (name/id???) that can be used to bring back the
birthdate.

Depending on how that table is set up, you may be able to use =vlookup() or
=index(match()).

Debra Dalgleish has some nice instructions for =vlookup() at:
http://www.contextures.com/xlFunctions02.html

and you didn't ask, but she has some nice instructions for =index(match()) at:
http://www.contextures.com/xlFunctions03.html

Vicky wrote:
>
> Hi,
>
> Can someone please help? I've got quite a lot of data that I want to sort
> by the persons date of birth, but because I have cells with formula in it
> (this works out the persons age) the sort function is changing the formulas
> so the formulas no longer work becuase it changes the cell where it is
> getting the data from. Does anyone have any ideas how this could be fixed??
>
> Thanks!

--

Dave Peterson

Vicky
either your formula are absolute eg $a$1 (must take off before sorting) or
you are not sorting all data including formula please give example of layout
if this doesnot help
Tina

"Vicky" wrote:

> Hi,
>
> Can someone please help? I've got quite a lot of data that I want to sort
> by the persons date of birth, but because I have cells with formula in it
> (this works out the persons age) the sort function is changing the formulas
> so the formulas no longer work becuase it changes the cell where it is
> getting the data from. Does anyone have any ideas how this could be fixed??
>
> Thanks!