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)

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.-

--

Gary''s Student Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535862