Question

我不知道这是否可行。

我有两张“投入”和“产出”的手册。

用户1 填写在“投入表”中

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

然后,在“产出”表中排栏 A组将使用用户输入的数值,复制A栏关于“投入”表和用户2的价值。

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

因此,我希望大家了解这一想法,用户1 得出一些细节,而用户2 则在这方面做了一些工作,并在“产出表”中增加了细节。页: 1 一种将两者联系在一起的“指数”价值。

我的问题是,如果用户2进入其中的细节,然后回到“投入”表并预示某种格式,那么“产出”表中的数值将不再匹配,因为A栏将作改动,以反映这种操作,其余部分将保持不变。

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

这不必为用户试图进行的临时性搜查而努力,我只是想在“投入”表上加顿,例如“按名称排列”、“按地点”等。

Regards

页: 1

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.

页: 1

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

这可能需要一些工作(例如,你可能需要事后重发3张,你可能需要重估,而不是仅仅过去,否则,你会破坏过去自我参照的模式),但基本想法应当发挥作用。

友情链接