Sort a column of formulas

[hmm]

Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I have
formulas, each one referring to the neighbor cell in column A.

I want to sort column B ascending without touching column A. For example,
if the smallest value was in B6, I want the formula in B6--including its
reference to A6-- to be transferred to B1, etc.

I tried doing this in my Excel 2000 and it did not work. Should it? If
yes, what could be the problem? If no, is there another way to achieve the
same result? Thanks.

Thanks.

[Bryan Hessey]

I imagine that after you did your sort the cells were re-located, then the formula re-calculated, and if it were the same relative formula in each cell guess it would look like it didn't sort at all.

You could Copy column B, then Paste Special = Values back over itsself before the sort.

Amended:
Alternately, to retain the formula reference to the row it came from, you need to re-write all formula to non-incrementing formula, ie, in B2 refer to A$2, in B3 refer to A$3, in B6 refer to A$6 etc

Hope this helps

--

Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I have
formulas, each one referring to the neighbor cell in column A.

I want to sort column B ascending without touching column A. For example,
if the smallest value was in B6, I want the formula in B6--including its
reference to A6-- to be transferred to B1, etc.

I tried doing this in my Excel 2000 and it did not work. Should it? If
yes, what could be the problem? If no, is there another way to achieve the
same result? Thanks.

Thanks.

[hmm]

What you suggested would not work because I want to keep the formulas, in
case column A values change. However, your reply did hint at the solution:
by using absolute references, I was able to sort the formulas. Now the next
question is how to convert all the relative references to absolute ones,
without having to go to every cell one by one and pressing F4!

"Bryan Hessey" wrote:

>
> I imagine that after you did your sort the cells were re-located, then
> the formula re-calculated, and if it were the same relative formula in
> each cell guess it would look like it didn't sort at all.
>
> You could Copy column B, then Paste Special = Values back over itsself
> before the sort.
>
> Hope this helps
>
> --
>
>
> hmm Wrote:
> > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I
> > have
> > formulas, each one referring to the neighbor cell in column A.
> >
> > I want to sort column B ascending without touching column A. For
> > example,
> > if the smallest value was in B6, I want the formula in B6--including
> > its
> > reference to A6-- to be transferred to B1, etc.
> >
> > I tried doing this in my Excel 2000 and it did not work. Should it?
> > If
> > yes, what could be the problem? If no, is there another way to achieve
> > the
> > same result? Thanks.
> >
> > Thanks.
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=535862
>
>

[Bryan Hessey]

Perhaps in column C you could put

="A"&MATCH(SMALL(B$1:B$10,ROW()),B$1:B$10,0)

to show the ascending order of column B by reference to column A.

--

What you suggested would not work because I want to keep the formulas, in
case column A values change. However, your reply did hint at the solution:
by using absolute references, I was able to sort the formulas. Now the next
question is how to convert all the relative references to absolute ones,
without having to go to every cell one by one and pressing F4!

"Bryan Hessey" wrote:

>
> I imagine that after you did your sort the cells were re-located, then
> the formula re-calculated, and if it were the same relative formula in
> each cell guess it would look like it didn't sort at all.
>
> You could Copy column B, then Paste Special = Values back over itsself
> before the sort.
>
> Hope this helps
>
> --
>
>
> hmm Wrote:
> > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I
> > have
> > formulas, each one referring to the neighbor cell in column A.
> >
> > I want to sort column B ascending without touching column A. For
> > example,
> > if the smallest value was in B6, I want the formula in B6--including
> > its
> > reference to A6-- to be transferred to B1, etc.
> >
> > I tried doing this in my Excel 2000 and it did not work. Should it?
> > If
> > yes, what could be the problem? If no, is there another way to achieve
> > the
> > same result? Thanks.
> >
> > Thanks.
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=535862
>
>

[Gary''s Student]

Do you have any interest in a VBA solution?
--
Gary's Student


"hmm" wrote:

> What you suggested would not work because I want to keep the formulas, in
> case column A values change. However, your reply did hint at the solution:
> by using absolute references, I was able to sort the formulas. Now the next
> question is how to convert all the relative references to absolute ones,
> without having to go to every cell one by one and pressing F4!
>
> "Bryan Hessey" wrote:
>
> >
> > I imagine that after you did your sort the cells were re-located, then
> > the formula re-calculated, and if it were the same relative formula in
> > each cell guess it would look like it didn't sort at all.
> >
> > You could Copy column B, then Paste Special = Values back over itsself
> > before the sort.
> >
> > Hope this helps
> >
> > --
> >
> >
> > hmm Wrote:
> > > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I
> > > have
> > > formulas, each one referring to the neighbor cell in column A.
> > >
> > > I want to sort column B ascending without touching column A. For
> > > example,
> > > if the smallest value was in B6, I want the formula in B6--including
> > > its
> > > reference to A6-- to be transferred to B1, etc.
> > >
> > > I tried doing this in my Excel 2000 and it did not work. Should it?
> > > If
> > > yes, what could be the problem? If no, is there another way to achieve
> > > the
> > > same result? Thanks.
> > >
> > > Thanks.
> >
> >
> > --
> > Bryan Hessey
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> > View this thread: http://www.excelforum.com/showthread...hreadid=535862
> >
> >

[Bryan Hessey]

Hi Gary''s Student,

I wonder, if the formula in column B is, as is indicated, a standard incrementing formula, copied down the column, then the sequence would normally be the same sequence as column A.

If this is true then, assuming the formula were something like (say) *4+2, the formula from B1 downwards should be:

=INDIRECT("A"&MATCH(SMALL(A$1:A$10,ROW()),A$1:A$10,0))*4+2&" from A"&MATCH(SMALL(A$1:A$10,ROW()),A$1:A$10,0)

this would be more informative in that the OP would not need to visit each cell in B to see where the formula originated, and would be in ascending sequence.

note, it doesn't handle duplicates, for that one would need to use column C as =A1+row()/100000000 and in column B from B1 onwards use
=INDIRECT("A"&MATCH(SMALL(C$1:C$10,ROW()),C$1:C$10,0))*4+2&" from A"&MATCH(SMALL(C$1:C$10,ROW()),C$1:C$10,0)
column C could remain hidden.

--

Do you have any interest in a VBA solution?
--
Gary's Student


"hmm" wrote:

> What you suggested would not work because I want to keep the formulas, in
> case column A values change. However, your reply did hint at the solution:
> by using absolute references, I was able to sort the formulas. Now the next
> question is how to convert all the relative references to absolute ones,
> without having to go to every cell one by one and pressing F4!
>
> "Bryan Hessey" wrote:
>
> >
> > I imagine that after you did your sort the cells were re-located, then
> > the formula re-calculated, and if it were the same relative formula in
> > each cell guess it would look like it didn't sort at all.
> >
> > You could Copy column B, then Paste Special = Values back over itsself
> > before the sort.
> >
> > Hope this helps
> >
> > --
> >
> >
> > hmm Wrote:
> > > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10, I
> > > have
> > > formulas, each one referring to the neighbor cell in column A.
> > >
> > > I want to sort column B ascending without touching column A. For
> > > example,
> > > if the smallest value was in B6, I want the formula in B6--including
> > > its
> > > reference to A6-- to be transferred to B1, etc.
> > >
> > > I tried doing this in my Excel 2000 and it did not work. Should it?
> > > If
> > > yes, what could be the problem? If no, is there another way to achieve
> > > the
> > > same result? Thanks.
> > >
> > > Thanks.
> >
> >
> > --
> > Bryan Hessey
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> > View this thread: http://www.excelforum.com/showthread...hreadid=535862
> >
> >

[Gary''s Student]

I like your approach:

1. It does not disturb the formulae in column B
2. It provides the sort
3. It avoids VBA
--
Gary's Student


"Bryan Hessey" wrote:

>
> Perhaps in column C you could put
>
> ="A"&MATCH(SMALL(B$1:B$10,ROW()),B$1:B$10,0)
>
> to show the ascending order of column B by reference to column A.
>
> --
>
> hmm Wrote:
> > What you suggested would not work because I want to keep the formulas,
> > in
> > case column A values change. However, your reply did hint at the
> > solution:
> > by using absolute references, I was able to sort the formulas. Now the
> > next
> > question is how to convert all the relative references to absolute
> > ones,
> > without having to go to every cell one by one and pressing F4!
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > I imagine that after you did your sort the cells were re-located,
> > then
> > > the formula re-calculated, and if it were the same relative formula
> > in
> > > each cell guess it would look like it didn't sort at all.
> > >
> > > You could Copy column B, then Paste Special = Values back over
> > itsself
> > > before the sort.
> > >
> > > Hope this helps
> > >
> > > --
> > >
> > >
> > > hmm Wrote:
> > > > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10,
> > I
> > > > have
> > > > formulas, each one referring to the neighbor cell in column A.
> > > >
> > > > I want to sort column B ascending without touching column A. For
> > > > example,
> > > > if the smallest value was in B6, I want the formula in
> > B6--including
> > > > its
> > > > reference to A6-- to be transferred to B1, etc.
> > > >
> > > > I tried doing this in my Excel 2000 and it did not work. Should
> > it?
> > > > If
> > > > yes, what could be the problem? If no, is there another way to
> > achieve
> > > > the
> > > > same result? Thanks.
> > > >
> > > > Thanks.
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=535862
> > >
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=535862
>
>

[Bryan Hessey]

All true, . . . but is it of any use to the OP?

--

I like your approach:

1. It does not disturb the formulae in column B
2. It provides the sort
3. It avoids VBA
--
Gary's Student


"Bryan Hessey" wrote:

>
> Perhaps in column C you could put
>
> ="A"&MATCH(SMALL(B$1:B$10,ROW()),B$1:B$10,0)
>
> to show the ascending order of column B by reference to column A.
>
> --
>
> hmm Wrote:
> > What you suggested would not work because I want to keep the formulas,
> > in
> > case column A values change. However, your reply did hint at the
> > solution:
> > by using absolute references, I was able to sort the formulas. Now the
> > next
> > question is how to convert all the relative references to absolute
> > ones,
> > without having to go to every cell one by one and pressing F4!
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > I imagine that after you did your sort the cells were re-located,
> > then
> > > the formula re-calculated, and if it were the same relative formula
> > in
> > > each cell guess it would look like it didn't sort at all.
> > >
> > > You could Copy column B, then Paste Special = Values back over
> > itsself
> > > before the sort.
> > >
> > > Hope this helps
> > >
> > > --
> > >
> > >
> > > hmm Wrote:
> > > > Suppose I have ten numbers in cells A1 - A10. In cells B1 - B10,
> > I
> > > > have
> > > > formulas, each one referring to the neighbor cell in column A.
> > > >
> > > > I want to sort column B ascending without touching column A. For
> > > > example,
> > > > if the smallest value was in B6, I want the formula in
> > B6--including
> > > > its
> > > > reference to A6-- to be transferred to B1, etc.
> > > >
> > > > I tried doing this in my Excel 2000 and it did not work. Should
> > it?
> > > > If
> > > > yes, what could be the problem? If no, is there another way to
> > achieve
> > > > the
> > > > same result? Thanks.
> > > >
> > > > Thanks.
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php...o&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=535862
> > >
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=535862
>
>