I have 3 columns that Iwant to sort and two have formulas that are linked to
other cells in the same worksheet. Every time I select the cells I want to
sort, my formulas do not stay with the original cell. Here is an example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and all.

Please Help!

Lisa,

You need to highlight the entire range when sorting.
--
http://HelpExcel.com




"Lisa H" wrote:

I have 3 columns that Iwant to sort and two have formulas that are linked to
other cells in the same worksheet. Every time I select the cells I want to
sort, my formulas do not stay with the original cell. Here is an example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and all.

Please Help!

Excerpt from http://www.exceltip.com/st/Sorting_R...cture/245.html

"Do not select a column or a row in a List before sorting; instead, select only a single cell. Clicking the Sort icon automatically sorts the entire List and the data will be sorted according to the selected cell's field."

Looks like only a single cell should be highlighted when it is a *List*. Interesting!

Anyone wants to shed some light on this?

Also, from the link: "Be careful when sorting data if there are formulas in the cells........"

Epinn

"galimi" wrote in message ...
Lisa,

You need to highlight the entire range when sorting.
--
http://HelpExcel.com




"Lisa H" wrote:

I have 3 columns that Iwant to sort and two have formulas that are linked to
other cells in the same worksheet. Every time I select the cells I want to
sort, my formulas do not stay with the original cell. Here is an example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and all.

Please Help!

Hi Epinn

Firstly, I would not regard this source of information as in any way the
definitive answer.

However, If you have used
InsertTable from XL2007 or
DataList from XL2003,
then indeed if you do select any cell within the List and click sort, it
will sort the whole table according to the values of the column
containing that cell.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Excerpt from
http://www.exceltip.com/st/Sorting_R...cture/245.html

"Do not select a column or a row in a List before sorting; instead,
select only a single cell. Clicking the Sort icon automatically sorts
the entire List and the data will be sorted according to the selected
cell's field."

Looks like only a single cell should be highlighted when it is a *List*.
Interesting!

Anyone wants to shed some light on this?

Also, from the link: "Be careful when sorting data if there are formulas
in the cells........"

Epinn

"galimi" wrote in message
...
Lisa,

You need to highlight the entire range when sorting.
--
http://HelpExcel.com




"Lisa H" wrote:

I have 3 columns that Iwant to sort and two have formulas that are
linked to
other cells in the same worksheet. Every time I select the cells I
want to
sort, my formulas do not stay with the original cell. Here is an
example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and
all.

Please Help!

Thank you, Roger.

Firstly, I would not regard this source of information as in any way the
definitive answer. <<

This is what I want to hear. I need someone to verify, as I know not all the info on the net is reliable.

Based on the OP's post, I did an experiment and learned something.

I highlighted the entire range and tried to sort by clicking the sorting icon. Sometimes it worked and sometimes it didn't. It all depended on how the formulae were set up.

e.g. Range is A1:C4 and is highlighted for sorting purpose.

If formula in C2 is =B2*10, in C3 is =B3*10, in C4 is =B4*10, then sorting gives the correct result.

This is because the formulae refer to the cells *within* the range??

If I change the formulae to read =F2*10 etc., then sorting won't work even though I highlight the range A1:C4 or even include the column F.

But if I change the formulae to absolute reference i.e. =$F$2*10 etc., then highlighting A1:C4 and doing the sort works fine.

I wonder if this could be something similar to what the OP encountered. She did mention that the formulae linked to other cells in the worksheet. The cells are outside the range that she wants to sort? Is absolute reference used? Thank you for her post as I have learned something.

Roger or anyone, please correct me if I am wrong. Roger, as always, I appreciate your guidance. I want to move beyond Excel but I keep coming back as there are wonderful teachers like you in this forum.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

Firstly, I would not regard this source of information as in any way the
definitive answer.

However, If you have used
InsertTable from XL2007 or
DataList from XL2003,
then indeed if you do select any cell within the List and click sort, it
will sort the whole table according to the values of the column
containing that cell.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Excerpt from
http://www.exceltip.com/st/Sorting_R...cture/245.html

"Do not select a column or a row in a List before sorting; instead,
select only a single cell. Clicking the Sort icon automatically sorts
the entire List and the data will be sorted according to the selected
cell's field."

Looks like only a single cell should be highlighted when it is a *List*.
Interesting!

Anyone wants to shed some light on this?

Also, from the link: "Be careful when sorting data if there are formulas
in the cells........"

Epinn

"galimi" wrote in message
...
Lisa,

You need to highlight the entire range when sorting.
--
http://HelpExcel.com




"Lisa H" wrote:

I have 3 columns that Iwant to sort and two have formulas that are
linked to
other cells in the same worksheet. Every time I select the cells I
want to
sort, my formulas do not stay with the original cell. Here is an
example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and
all.

Please Help!

Hi Epinn

Provide I select A1:F5 before doing the sort, then formulae like F2*10
will sort OK.
The values will be different, because the cell may well now have change
to =F5*10 because of the repositioning caused by the sort. This is
merely a change due to using Relative Referencing rather than Absolute
Referencing, as opposed to Sort not working.

I would still always select the range I wanted to Sort myself, rather
than Excel choosing, UNLESS I am using a Table in XL2007 where I have
(so far) found it to be very reliable.
--
Regards

Roger Govier


"Epinn" wrote in message
...
Thank you, Roger.

Firstly, I would not regard this source of information as in any way
the
definitive answer. <<

This is what I want to hear. I need someone to verify, as I know not
all the info on the net is reliable.

Based on the OP's post, I did an experiment and learned something.

I highlighted the entire range and tried to sort by clicking the sorting
icon. Sometimes it worked and sometimes it didn't. It all depended on
how the formulae were set up.

e.g. Range is A1:C4 and is highlighted for sorting purpose.

If formula in C2 is =B2*10, in C3 is =B3*10, in C4 is =B4*10, then
sorting gives the correct result.

This is because the formulae refer to the cells *within* the range??

If I change the formulae to read =F2*10 etc., then sorting won't work
even though I highlight the range A1:C4 or even include the column F.

But if I change the formulae to absolute reference i.e. =$F$2*10 etc.,
then highlighting A1:C4 and doing the sort works fine.

I wonder if this could be something similar to what the OP encountered.
She did mention that the formulae linked to other cells in the
worksheet. The cells are outside the range that she wants to sort? Is
absolute reference used? Thank you for her post as I have learned
something.

Roger or anyone, please correct me if I am wrong. Roger, as always, I
appreciate your guidance. I want to move beyond Excel but I keep coming
back as there are wonderful teachers like you in this forum.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

Firstly, I would not regard this source of information as in any way the
definitive answer.

However, If you have used
InsertTable from XL2007 or
DataList from XL2003,
then indeed if you do select any cell within the List and click sort, it
will sort the whole table according to the values of the column
containing that cell.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Excerpt from
http://www.exceltip.com/st/Sorting_R...cture/245.html

"Do not select a column or a row in a List before sorting; instead,
select only a single cell. Clicking the Sort icon automatically sorts
the entire List and the data will be sorted according to the selected
cell's field."

Looks like only a single cell should be highlighted when it is a *List*.
Interesting!

Anyone wants to shed some light on this?

Also, from the link: "Be careful when sorting data if there are formulas
in the cells........"

Epinn

"galimi" wrote in message
...
Lisa,

You need to highlight the entire range when sorting.
--
http://HelpExcel.com




"Lisa H" wrote:

I have 3 columns that Iwant to sort and two have formulas that are
linked to
other cells in the same worksheet. Every time I select the cells I
want to
sort, my formulas do not stay with the original cell. Here is an
example of
what I have:

Alissa 2 $100000.00

I need this information to stay together no matter what, formulas and
all.

Please Help!