If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linked cells between worksheets and sorting
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 |
#2
|
|||
|
|||
Linked cells between worksheets and sorting
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 On Wed, 18 Nov 2009 09:18:03 -0800, Emma wrote: 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 |
#3
|
|||
|
|||
Linked cells between worksheets and sorting
Great, thanks for prompt reply, will give a shot.
"Gord Dibben" wrote: 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 On Wed, 18 Nov 2009 09:18:03 -0800, Emma wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|