Sorting data without mixing rows

Question

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

=IF(General!A2="","",General!A2)

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.

Answer 1

@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.

Answer 2

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.

Answer 3

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.

-AJ