I’m having what appears to be a common problem with sorting tables in Excel, when they contain formulas.
However, while most other people seem to have formulas in their tables referring to other tables or other sheets, I have formulas referring to other rows in the same table.
My table in the sheet at A1
Sorry about the “equals”, but it kept saying I had code in my question when I put “=A2” etc., so I had to get creative!
| Name | Wife |
|---|---|
| Richard | equals A3 |
| Dulcie | equals A2 |
| Steve | equals A5 |
| Mary | equals A4 |
| Chris | equals A7 |
| Paul | equals A6 |
So it displays Richard’s wife as Dulcie and Dulcie’s wife as Richard etc.
All well and good!
Then I sort column 1 alphabetically A-Z and all the formulas in column 2 muck up and no-one’s wife is correct any more.
I can see what’s happening – when Steve moves down 3 places to the bottom, the formula goes down 3 rows too, from A5 to A8, which doesn’t exist.
Anyone know of a clever way I can avoid this?
I tried absolute references, but that means they don’t change at all, so Steve’s wife stays pointing at $A$5, which is now Paul !
(Yes I know I could use a lookup function in some way, but just want to point to the cell)
Where I’m actually having the problem is in a larger spreadsheet with a much bigger formula in col 2, but still just trying to reference other rows in col 1
Hopefully that’s enough info to explain my problem?
Thanks