Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ken Rock
 
Posts: n/a
Default 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


  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #3   Report Post  
Ken Rock
 
Posts: n/a
Default

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
  #4   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #5   Report Post  
Ken Rock
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort Preferences Problem Chris Excel Discussion (Misc queries) 2 June 13th 05 02:39 AM
Links Problem DSE Excel Worksheet Functions 2 March 25th 05 02:53 AM
update links - problem Micha³ S Excel Discussion (Misc queries) 1 February 18th 05 01:58 AM
Update Links - Problem Metallo Links and Linking in Excel 2 January 25th 05 05:42 PM
Sort Problem jdb Excel Discussion (Misc queries) 1 January 11th 05 12:05 AM


All times are GMT +1. The time now is 11:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"