Need help sorting in Excel

Factual Questions
1

I have some rows in Excel with formulas that simply refer to values in other rows:

=A1
=A2
=A3
The original data is something like

Smith, Snuffy
Jones, Barnaby
Doe, Bambi

I want to leave the original data alone, but sort the rows with the formulas according to the data displayed by those formulas. I want the rows with formulas to show

Doe, Bambi
Jones, Barnaby
Smith, Snuffy

and therefore have formulas

=A3
=A1
=A2

But when I sort, the formulas are still intact, showing the same data as before. I am trying different things to narrow down the problem and getting even more bizarre results. Like switching a couple of rows around, and then sorting, and then those rows get completely different formulas.

I have scoured Excel Help but Excel Help is only good for the most straightforward issues. I tried MS Knowledgebase, but the closest I came was how to set up the formulas so the data displayed will not change if you sort the original data, kind of the converse of what I want.

2

I’m not getting it. Why don’t you just sort column A along with the rest of the data?

3

I just cut and pasted your exact names and used the links you listed - no problems resorting here. I just highlighted the cells with the links, sorted alphabetically and it worked like a charm (the formulas switched from A1, A2, A3 to A3, A2, A1).

4

Never mind. I see what you’re saying.

It works for me if I change the references to absolute references (=A1 becomes =$A$1, etc.). Then the results of the formulas sort correctly.

5

Hmmm. I wonder if I have some option set that is affecting this. I know that find & replace has an option to look at values vs. formulas but I didn’t see anything like that for sorting. Back to the drawing board. Thanks for the effort!

6

Sorry I couldn’t help more.

7

I think changing the formulas to be fixed (with a $ before the column letter and row number) as splatterpunk described is the only way to sort the formulas without the cell-referencing adjusting to the original data.

The fastest way to change all the formulas to fixed references is to hilight the entire column and doing a replace, with “A” in the “find” parameter and “A”, then selecting “Replace All”.

Unfortunately, each time you insert a new record you will have to resort the column with the formulas, but that’s not too much work since you will have to add a forumula in the new cell to pick up the new data.

8

That should read with “A” in the “find” parameter and “A” in the “Replace” parameter, then selecting “Replace All”.

Sorry 'bout that.

Cy

9

In the formula where you have formulas, why not just Copy-Paste Special-Values. Then you can sort them without messing with the original column.