sorting across sheets in excel

Question

I don't know if this is possible or not.

I have a workbook that has two sheets, "input" and "output"

user1 fills in the "input sheet"

Column A = name
Column B = Age
Column C = Location
Column D = Hight
..
..

Then on the "output" sheet Column A is set to copy the value from Column A on the "input" sheet and User2 fills in more details using the values that user1 has entered

Column B = eye colour
Column C = hair colour
Column D = number of fingers
..
..

So I hope you get the idea, User1 enters some details, and then User2 does some work with that and enters more details in the "output sheet. With Column A being the "index" value that links the two together.

My issue is that if User2 enters there details, and then goes back to the "input" sheet and preforms a sort, the values in the "output" sheet will no longer match, as while Column A will have changes to reflect the sort operation the rest will stay the same.

Is it possible to link rows between sheets, or to create a sort code that will run across both sheets and keep them consistence.

This does not have to work for ad-hoc searches that the user tries, I just want to put a button on the "input" sheet, for example to "sort by name", "sort by Location" etc

Regards

Aaron

Answer 1

If you're ok with having pre-set sorts, one solution is to mirror both values from sheet A and sheet B to sheet C, and then just sort that, and re-populate the values in sheet A and B with the new, sorted, results.

I.e.

Sheet1                   | Sheet 2                           | Sheet 3 (Hidden and named)
                         |                                   | 
Name     Age    etc,     | Eye Colour    Hair colour   etc.  | =Sheet1!A1 ... =Sheet2!A1

Then your sort button would call a sub something like:

Dim rngSortRange As Range, rngStartCell As Range, rngEndCell As Range
Set rngStartCell = Worksheets("Sheet_3_Name_Goes_Here").Range("A1")
Set rngEndCell = Worksheets("Sheet_3_Name_Goes_Here").Range( _
  rngStartCell.End(xlToRight).Column, _
  rngStartCell.End(xlDown).Row)
Set rngSortRange = Worksheets("Sheet_3_Name_Goes_Here").Range(rngStartCell, rngEndCell)
rngSortRange.Sort Key1:=<Column Number Here>, Order1:=xlAscending, Header:=xlYes
rngSortRange.Range(rngStartCell, _
                    Worksheets("Sheet_3_Name_Goes_Here").Range( _
                     Worksheets("Sheet 1").Range("A1").End(xlToRight).Column, _
                     rngEndCell.Row _
                    ) _
                   ).Copy
Worksheets("Sheet 1").Paste
rngSortRange.Range(Worksheets("Sheet_3_Name_Goes_Here").Range(
                     rngStartCell.Column + Worksheets("Sheet 1").Range("A1").End(xlToRight).Column, _
                     rngStartCell.Row _
                    ), _
                    rngEndCell _
                   ).Copy
Worksheets("Sheet 2").Paste

That might need some work (e.g. you might need to reset sheet3 afterwards, you might need to pastevalues rather than just paste, else you'll end up pasting self-referencing formulas), but the basic idea should work.