I have three sheets. First sheet renamed General:

Dave        Henderson      21/05/2014      
Elton       Rus            22/06/2014
John        Smith          20/05/2015

Second sheet renamed Licence:

Forename    Surname     Licence
Dave        Henderson   Car
Elton       Rus         Bus
John        Smith       HGV

Third sheet renamed Hire date:

Forename    Surname      Pass date
Dave        Henderson    29/05/2015
Elton       Rus          30/01/2015
John        Smith        01/04/2015

The first 2 columns (Forename and Surname) are copied with this formula


to the Licence and Hire date sheets.

The only column left to be filled in is the Licence column (in the first sheet - Licence) and the Pass date (in the second sheet - Hire date). I would like to use only the first sheet to add names and those names should copy into the other sheets. The thing is, when I am trying to sort the Surname in the first sheet (General) everything is mixing into the other sheets.

Is there a chance that I can add names only in the first sheet? After that I'd like those names to copy into the other sheets and not to mix with other sheets when I am trying to sort the names in the first sheet.

Spreadsheet Screenshot

  • Seems that is a typical relational database task, have you tried Access? – omegastripes May 24 '16 at 19:48
  • 2
    Look into VLOOKUP, INDEX and MATCH functions. Don't make cell references like that. – Mathieu Guindon May 24 '16 at 20:01

@Razwan, it's quit natural since Surname you copied from Sheet General to others and for this you have used the Formula. Because of that the Surnames are linked with the source (which is the General Sheet) and if the source is been changed obviously the linked data will be changed.

The Solution is, simply copy the Surnames from General sheet to others and Sort the Surnames it will not reshuffle the surnames in other sheets.


When I have data like this, I typically put all of the information into one Master Data sheet and then set up filters on the headers so that I can see only what I need to see.

If I really needed the License and Hire Date to be separate sheets, then I would either create PivotTables in separate sheets (for summary data) or use vlookup formulas as Mat's Mug suggested.

Doing it this way makes it easy as you only have to manage one set of data that flows throughout your entire workbook.


Just Create one more sheet at the start as Master with names and Surnames only and then refer that sheet values in General,Licences and Hire Tabs. Now whatever you sort in general,Licence or Hire Tab, will not affect each other.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.