Resolved Adressing in Excel

You may wonder why I am doing something this way, but I have a good reason. I’ve simplified a more complex structure to explain my query.
Example spread sheet: https://dl.dropbox.com/u/89946151/Example.xlsx
E1:E3 each contain a formula (=Bn).
When I sort range A1:B3 or D1:E3 into another order the values in B1:B3 do not reflect the corresponding values in E1:E3.
I need to be able to sort A1:B3 and D1:E3 into any order, at any time but still reflect the correct value next to each name.
Any assistance would be appreciated.

 

Change the formula in E1 from "=B1" to "=$B$1 ". (Click on E1, press [F2] to edit, press [F4] then press [Enter]).

Now do the same in E2, E3, etc.

 

That doesn't help me when I sort E1:F3 then sort A1:B3.
What I'm trying to do is have F1, F2 and F3 always referencing the cell on column B corresponding to the same name (Bill Smith, John Jones and Peter Pan) in column E.

 

Add the "$ "'s to all of your forumlas.

 

I did - the only formulas are in E1, E2 and E3. B1, B2 and B3, contain entered values 1,2 and 3.

 

I'm lost; I don't see anything in F1-F3.

 

Sorry, sorry, sorry - by F1, F2 and F3 I meant E1, E2 and E3

 

A1: Bill Smith
A2: John Jones
A3: Peter Pan

B1: 1
B2: 2
B3: 3

D1: $A$1
D2: $A$2
D3: $A$3

E1: $B$1
E2: $B$2
E3: $B$3

?

 

If I understand you correctly (that is a big if), there are two ways you can accomplish what you want. (1) Select the entire range of the cells you want sorted (if you are working with the first group in your example, this would be A1 through B3). Then go to the Data ribbon, click on the Sort option, and then the left-hand option would be the A column. OK out and that should do it.

(2) Alternately, simply select the column that you want to sort (A1-A3). Click on Sort, and Excel will tell you that there is data attached, do you want to expand the selection. Hit yes/ok, and Excel will sort your data based on column A, but keep the associated data in column B aligned as it was originally.

Instructions are for Excel 2010. Not sure what E2007 offers, but it should be rather similar.

 

I certainly know how to sort, so sorting the data is not the problem - it's the end result of cells correctly referencing other cells after any combination of sorting that is a liitle perplexing.
The previous post (5th November 2012, 10:25) I think helps me out (I should have thought of it myself - talk about missing the bleedin' obvious).
As I said earlier my project is more complex than the example so I need to check it out to be sure it's doing what I need.
Thanks anyway

 

If the problem has been resolved ...