Linked cells between worksheets and sorting

[Emma]

Hello,
I have 500+ rows of data on a worksheet called 'Personal Details'. From
this worksheet, two columns (first name and surname) feed into other
worksheets by cell linking e.g. into the 'Course Information' worksheet
(links are all within same workbook). The other worksheets have additional
columns of data e.g. 'Course Information' has a column called Tutor, the
tutors names are linked back into the 'Personal Details' worksheet.

The problem is for example, when I sort the Surname column on 'Personal
Details' A-Z, the tutor name column (linked from 'Course Information') is
wrong. Or, if I sort the Tutor Name A-Z on 'Course Information' worksheet,
the student names become muddled.

Is there a way to fix it? (If Column B is called Surname on 'Personal
Details', it might be linked as column E on another sheet).

Regards,
Emma

 

[Gord Dibben]

Entered in A1 of Course Information sheet and copied down.

=INDIRECT("'Personal Details'!A" & ROW())

Will sort properly when Column A of Personal Details sheet is sorted.

Same type of formula in Personal Details sheet linking to Course Information
sheet Tutors column.


Gord Dibben MS Excel MVP

 

[Emma]

Great, thanks for prompt reply, will give a shot.