Sorting formulas incorrectly

[armitageshanks]

Hi,
I have a database that you input information into, looks a bit like
this:

NAME AGE EMPLOYEE CODE ADDRESS
SMITH, J 24 301 24 Millhaven
THOMAS 32 405 42 Holly Road
JOHNSON, T 46 401 12 Roode Road

You get the idea, basically in the forth column is a formula that
refers to a second sheet that is identical to this one but includes
ethnicity, sex etc, for monitoring purposes and gets all its
information from this sheet. This sheet is the basic one with as few a
formulas on it so that the people using it don't mess it up.
The third row is where you input a 3 digit number which brings up
their address, I know you could use the INDEX formula, but it needs to
be this way.
The only formula on this sheet is =Sheet2!D2, for Smith address and so
on, the only problem is that when you sort, it doesn't sort the
formulas correctly on this sheet. I need them to stay =Sheet2!D2 on
sheet 1 D2, =Sheet2!D3 on sheet 1 D3, and so on. I've tried sticking
in the $ symbol in =Sheet2!D$2 but this doesn't work.
Any suggestions???? I'm pulling my hair out......
Regards,
Nigel

 

[Anon]

Hi,
I have a database that you input information into, looks a bit like
this:

NAME AGE EMPLOYEE CODE ADDRESS
SMITH, J 24 301 24 Millhaven
THOMAS 32 405 42 Holly Road
JOHNSON, T 46 401 12 Roode Road

You get the idea, basically in the forth column is a formula that
refers to a second sheet that is identical to this one but includes
ethnicity, sex etc, for monitoring purposes and gets all its
information from this sheet. This sheet is the basic one with as few a
formulas on it so that the people using it don't mess it up.
The third row is where you input a 3 digit number which brings up
their address, I know you could use the INDEX formula, but it needs to
be this way.
The only formula on this sheet is =Sheet2!D2, for Smith address and so
on, the only problem is that when you sort, it doesn't sort the
formulas correctly on this sheet. I need them to stay =Sheet2!D2 on
sheet 1 D2, =Sheet2!D3 on sheet 1 D3, and so on. I've tried sticking
in the $ symbol in =Sheet2!D$2 but this doesn't work.
Any suggestions???? I'm pulling my hair out......
Regards,
Nigel

Simply don't include column D in the sort!
To do this, select columns A to C before starting the sort process.

As a further explanation in case it helps, if more than one cell is
selected, Excel sorts just what is selected. If only one cell is selected,
Excel assumes a sort area which is all the data contiguous to the active
cell. (Anything separated by a blank row or column is excluded.)

 

[Dave Peterson]

If you have unique common keys ("Smith, J") in both sheets, you could replace
that =sheet2!d2 with a vlookup formula:

=vlookup(a2,sheet2!$a$2:$d$99,4,false)

 

[armitageshanks]

Hi,
Thanks for the tip, the only problem with it is that the columns go up
to about I and don't end at D, so everything before and after D, but
not D, will need to be sorted, I know I could move D to the front or
end and thus solve the problem, but that'd be defeating the object.
Any other ideas would be great.
Cheers,
Nigel