Question about sort

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gr8posts - 28 Oct 2007 02:13 GMT

I have a workbook with one sheet (Sheet1)
In cell a1 I have the value "1"
In cell b1 I have the formula "=a1"
In cell c1 I have the formula "=Sheet1!a1"

Select a1:c9 and do fill down. You obtain 3 identical columns with numbers
from 1 to 9 from top to bottom.

Try now to sort the area a1:c9 according to column a Descending (no headers).

Questions:
Is it normal that column c is in reverse order compared to a and b?
Is there any explanation about this behaviour?

Seems like formulas with references containing "!" are taken into
consideration by sort but they don’t adapt as others without "!" references.

Thank you in advance for you time.

Reply to this Message

gr8posts - 28 Oct 2007 02:46 GMT

I am sorry I am correcting my initial post.
It is not "Select a1:c9 and fill down"
but "Select a1:c1 drug down the small black square at the bottom right to
row 9"
Cell c9 shows "=Sheet1!A9"
#####
I have a workbook with one sheet (Sheet1)
In cell a1 I have the value "1"
In cell b1 I have the formula "=a1"
In cell c1 I have the formula "=Sheet1!a1"

Select a1:c1 drug down the small black square at the bottom right to row 9"
Cell c9 shows "=Sheet1!A9"

You obtain 3 identical columns with numbers from 1 to 9 from top to bottom.

Try now to sort the area a1:c9 according to column a Descending (no headers).

Questions:
Is it normal that column c is in reverse order compared to a and b?
Is there any explanation about this behaviour?

Seems like formulas with references containing "!" are taken into
consideration by sort but they don’t adapt as others without "!" references.

Thank you in advance for your time.

Reply to this Message

Ragdyer - 28 Oct 2007 04:56 GMT

Did you click in the cells in Column C?
Don't you see that Column C *DID* follow the sort key (Column A) and reverse
itself?
So now Row 9 is on top, and Row 1 is at the bottom.
And it's returning exactly what it's supposed to ... the cell contents of
Column A,
Where Column A *reversed* itself!

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> I am sorry I am correcting my initial post.
> It is not "Select a1:c9 and fill down"
[quoted text clipped - 22 lines]
>
> Thank you in advance for your time.

Reply to this Message

Gord Dibben - 28 Oct 2007 05:07 GMT

It does sort properly in my estimation.

Take a look at column C after the descending sort.

C1 reads =Sheet1!A9

A9 is 1 due to the sort.

Gord Dibben MS Excel MVP

>I am sorry I am correcting my initial post.
>It is not "Select a1:c9 and fill down"
[quoted text clipped - 22 lines]
>
>Thank you in advance for your time.

Reply to this Message

gr8posts - 28 Oct 2007 09:58 GMT

Hi
of course I had checked contents of column C, that is why I had said
"Seems like formulas with references containing "!" are taken into
consideration by sort but they don’t adapt as others without "!" references."

Lets put it this way then
Why formulas in column C do not adapt references as cells in column B
why C1 becomes =sheet1!A9 while B1 remains =A1
I hope it is more clear now what my questions are about.

Note: sheet1!A1 is not an absolute reference since it changes if you copy to
another cell. Is this correct?

If I assume sheet1!A1 is a relative reference it should be adapted as in
column B isn't it ?

Thanks again

Reply to this Message

Gord Dibben - 28 Oct 2007 15:44 GMT

Technically =Sheet1!A1 is a 3-D reference normally used to refer to another
sheet.

As such it sorts in that style.

If you placed 1-9 in Sheet2 column A and entered =Sheet2!A1 in C1 of Sheet1
and copied down then sorted on column A, you would expect column C to follow the
3-D reference to Sheet2 which it does.

Gord

>Hi
>of course I had checked contents of column C, that is why I had said
[quoted text clipped - 13 lines]
>
>Thanks again

Reply to this Message

gr8posts - 28 Oct 2007 20:16 GMT

Very good Gord,
I will try to remember what you said:
It is a 3D formula and as such it is sorted that way by Excel.

I will try to investigate further what rules of sorting are implemented to
3D formulas since the result is quite confusing and sometimes unpredictable.
To be clear what I mean I give as an example again my original table where
column C had formula =Sheet1!A1 (copied dwn), and sorting area A1:C9 with
column C as key1. In this case if you implement Asc sorting nothing happens
and if you implement Desc sorting columns A and B keep changing from 1->9 to
9->1 alternatively !!!

Anyway thank you very much for your replies.
They are of real help for further investigation.
To my opinion I think the sheet name should be eliminated during formula
validation before acceptance if it refers to the same sheet as the cell
containing the entered formula.
I am not quite sure if formulas with one level at the Z axis should be
considered as 3D especially if they refer to the same sheet (Z level). This
is just my point of view.
For me until now a 3D formula was Sheet1:sheet3!A1:D9 (3 levels of Z 4
levels of Y and 9 levels of X)

Thanks again for your time.
Thanos

PS. My conclusion is DO NOT USE in the same sort 3D references and the
references themselves.

> Technically =Sheet1!A1 is a 3-D reference normally used to refer to another
> sheet.
[quoted text clipped - 24 lines]
> >
> >Thanks again

Reply to this Message

Gord Dibben - 28 Oct 2007 20:33 GMT

It is my assumption that Excel recognizes the ! after a sheet name as part of
a 3-D or "linked" reference and treats it as such even when referring to the
same sheet it is written in.

Would be nice to have it rejected if referring to same sheet when it could
potentially cause a problem as you have pointed out.

Gord

>Very good Gord,
>I will try to remember what you said:
[quoted text clipped - 53 lines]
>> >
>> >Thanks again

Reply to this Message