Sort - links problem
 

Hi,

Can anyone advise me on this?

I have a 3 x 3 matrix with columns A,B,C and rows 1,2,3. Each cell contains
a simple link such as (=J6) but all 9 links are different. I write down a 9
x 2 matrix A1,B1,C1,A2,B2,C2,A3,B3,C3 in the first column with the values
of these cells in the second column. When I sort the first column to
A1,A2,A3,B1,B2 etc I expect the corresponding cells containing the values to
move to match the cell addresses. However, I find that the contents remain
in the same vertical order even though the cell addresses have been sorted.
What am I missing here?
TIA Ken Rock

Not sure I understand what you have in your second column, but if it is
=A1, =B1 etc. then those formulas will be adjusted when you sort. So,
for example, if the formula which referenced B1 was moved down 2 cells
it would then reference B3.

If you make the formulas reference absolute rows then the sort will
work as I think you intended. =A$1, =B$1 etc. The row numbers will
not change when the formulas are sorted.

Alternatively you could use the INDIRECT function.
=INDIRECT(H2) where H2 contains "A1"

Remember to sort both columns of the table together!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill Manville wrote:
Not sure I understand what you have in your second column, but if it is
=A1, =B1 etc. then those formulas will be adjusted when you sort. So,
for example, if the formula which referenced B1 was moved down 2 cells
it would then reference B3.

If you make the formulas reference absolute rows then the sort will
work as I think you intended. =A$1, =B$1 etc. The row numbers will
not change when the formulas are sorted.

Alternatively you could use the INDIRECT function.
=INDIRECT(H2) where H2 contains "A1"

Remember to sort both columns of the table together!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi Bill,

Many thanks for your prompt response. I apologise for the inadequate
description of my problem - the second column did indeed contain =A1 etc.
The original problem concerns a matrix of 256 rows and 16 columns.
When I use your suggested INDIRECT function, such as =INDIRECT(H2) I get
a #REF! error since cell H2 contains a formula. Since I am not familiar
with the INDIRECT function, I will have to find more information on it.
I want to re-arrange the 256 rows but keep the original formuli unaltered.
I have experimented with your suggestion of $A1 instead of A1 but I find
I need to change it to $A$1. Since I have 256 rows and 16 columns, I
will have to make many changes manually UNLESS....
Is there a way to alter the formula in each cell by adding the $ signs
using, perhaps, the Replace function without resorting to VBA?
Regards, Ken Rock

Ken Rock wrote:
When I use your suggested INDIRECT function, such as =INDIRECT(H2) I get
a #REF! error since cell H2 contains a formula


INDIRECT takes a string argument which is the address of the cell you want
to access the contents of.

=INDIRECT("H2") will give you the content of cell H2
=INDIRECT(H2) where H2 contains the value "A3" will give you the content of
cell A3.

Is there a way to alter the formula in each cell by adding the $ signs
using, perhaps, the Replace function without resorting to VBA?

Edit / Replace / = with =$ / Replace All will do the columns
Edit / Replace / A with A$ / Replace All will do references to column A
etc.

Or you could select all the cells and run the following macro

Sub MakeAbsolute()
Dim C As Range
For Each C In Selection.Cells
C.Formula = Application.ConvertFormula(C.Formula, xlA1, , True)
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill Manville wrote:
Ken Rock wrote:

When I use your suggested INDIRECT function, such as =INDIRECT(H2) I get
a #REF! error since cell H2 contains a formula



INDIRECT takes a string argument which is the address of the cell you want
to access the contents of.

=INDIRECT("H2") will give you the content of cell H2
=INDIRECT(H2) where H2 contains the value "A3" will give you the content of
cell A3.


Is there a way to alter the formula in each cell by adding the $ signs
using, perhaps, the Replace function without resorting to VBA?


Edit / Replace / = with =$ / Replace All will do the columns
Edit / Replace / A with A$ / Replace All will do references to column A
etc.

Or you could select all the cells and run the following macro

Sub MakeAbsolute()
Dim C As Range
For Each C In Selection.Cells
C.Formula = Application.ConvertFormula(C.Formula, xlA1, , True)
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill,

I am now well on my way to master Super Sudoku. Next week, the world.....

Regards, Ken Rock