在excel中分页 [英] sorting across sheets in excel

问题描述

我不知道这是否可行。

我有一个工作簿有两张输入和输出

user1填写输入表

列A =名称

列B =年龄

列C =位置

列D = Hight

..

..

然后在输出表上列A设置在输入表上复制列A中的值，User2使用user1输入的值填写更多详细信息

列B =眼睛颜色

列C =发色

列D =手指数

..

..

所以我希望你得到这个想法，User1输入一些细节，然后User2做了一些工作，并在输出表中输入更多的细节。列A是链接两个的索引值。我的问题是，如果User2进入细节，然后回到输入表并预先排序，输出表中的值将不会更长的匹配，as而列A将进行更改以反映排序操作，其余的将保持不变。

是否可以在工作表之间链接行，或创建一个排序代码，跨两张纸，保持一致。

这不需要用于用户尝试的特别搜索，我只想在输入工作表上放一个按钮，例如排序按名称，按位置排序等

注意

Aaron

解决方案

如果您可以使用预先设定的排序，则一个解决方案是将表A和表B中的值都映射到表C，然后再排序并且使用新的排序结果重新填充表A和B中的值。

即， b
$ b

  Sheet1 |表2 |表3（隐藏并命名）
 | | 
名称年龄等，|眼睛颜色头发颜色等= Sheet1！A1 ... = Sheet2！A1 
  

然后你的排序按钮会调用一个类似的东西：

  Dim rngSortRange As Range，rngStartCell As Range，rngEndCell As Range 
 Set rngStartCell = Worksheets（Sheet_3_Name_Goes_Here） .Range（A1）
设置rngEndCell =工作表（Sheet_3_Name_Goes_Here）。范围（_ 
 rngStartCell.End（xlToRight）.Column，_ 
 rngStartCell.End（xlDown）.Row ）
设置rngSortRange =工作表（Sheet_3_Name_Goes_Here）。范围（rngStartCell，rngEndCell）
 rngSortRange.Sort Key1：=<列号此处>，Order1：= xlAscending，标题：= xlYes 
 rngSortRange.Range（rngStartCell，_ 
工作表（Sheet_3_Name_Goes_Here）。范围（_ 
工作表（Sheet 1）。范围（A1）。End（xlToRight）.Column，_ 
 rngEndCell.Row _ 
）_ 
）.Copy 
工作表（Sheet 1）。粘贴
 rngSortRange.Range（Worksheet（Sheet_3_Name _Goes_Here）。Range（
 rngStartCell.Column + Worksheets（Sheet 1）。Range（A1）。End（xlToRight）.Column，_ 
 rngStartCell.Row _ 
） ，$ _ 
 rngEndCell _ 
）.Copy 
工作表（Sheet 2）。粘贴
  

这可能需要一些工作（例如您可能需要重新设置sheet3，您可能需要粘贴值而不是粘贴，否则您将最终粘贴自引用公式），但基本思想应该可以工作。

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

解决方案

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.

这篇关于在excel中分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！