Hi there,
It sounds like you're having trouble with maintaining cell references after sorting in Excel. When you use relative cell references, they should change with a sort in the precedent worksheet, but it seems like that's not happening for you.
One thing to keep in mind is that when you link to a cell in another worksheet, the link is referring to the cell itself, not the content of the cell. So when you sort the precedent worksheet, the reference in the descendant worksheet won't change.
One workaround for this is to add a unique identifier to each row/record in your table, and then use the
VLOOKUP function to retrieve the correct record/value based on that identifier. This way, even if the rows are sorted, the VLOOKUP function will still be able to find the correct record.
- Add a unique identifier to each row/record in your table.
- Use the VLOOKUP function to retrieve the correct record/value based on that identifier.
As for your question about a global setting that might be causing this problem, I'm not aware of any such setting. It's possible that there's a formatting issue or some other setting that's causing the problem, but without more information it's hard to say for sure.
Formula:
//Sample macro code
Sub SortData()
'Sort data in ascending order based on column A
Range("A1").CurrentRegion.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes
End Sub