Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Preferences Problem | Excel Discussion (Misc queries) | |||
Links Problem | Excel Worksheet Functions | |||
update links - problem | Excel Discussion (Misc queries) | |||
Update Links - Problem | Links and Linking in Excel | |||
Sort Problem | Excel Discussion (Misc queries) |