Question about sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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!
 
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
 
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
 
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
 
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.
 
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
 
Back
Top