Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Now I have a worksheet named sheet1 like the left side of image enter image description here

I want to automatic copy all rows of sheet1 that have the column C = NO to worksheet sheet2 like the right side of image. And each time I insert new row of sheet1 with C column = NO, it will automatic update sheet2 with index column E auto increase.

Can anyone help me?

share|improve this question
up vote 1 down vote accepted

This sounds like a job for a PivotTable but you're going to need row headers.

First, setup a dynamic named range that will capture your list in A:C and grow along with it. To do this, click on the Name Manager in the Formulas ribbon. Click New, give it some name (I called mine rngPivotData) and use this for its "Refers to:" formula:


Screenshot 1

Next, add a PivotTable by clicking in cell E1 and then clicking Pivot Table on the Insert riboon.

Screenshot 2

When it asks you the table or range to use, type the name you chose for the dynamic named range.

Screenshot 3

Right-click somewhere in the PivotTable and click on "PivotTable Options...". In the Display tab, check "Classic PivotTable layout". This will help give the look you want.

Screenshot 4

Drag all three headers into the Row Labels section of the PivotTable pane and then filter the Yes/No field for just those that are No. Screenshot 6

Here's the final product. Anytime you make changes, you can just right-click anywhere in the PivotTable and click Refresh. Screenshot 6

share|improve this answer

As I don't have Excel 2007 with me, I will try to describe the process the best I can.

On Sheet2, equal all data to the corresponding data in Sheet1, as following:

enter image description here

Excel Web App doesn't allow formulas on the Headers while sorting, so I wrote the names by hand; however in Excel 2003 I don't have that particular problem. Extend that formula to the last row (or infinity) of your data range. You now have the same Data in both sheets, except you can filter in separate sheets without altering the others.

Select the A and B columns, then click Data > Filter. Some arrow buttons will appear on the headers. Press the button on the Type header, and then select the "NO" option only. The end result is this:

enter image description here

As reference I used this source.

The following would be an "archaic" way of doing this, I just remembered it earlier and it would be of value to someone.

I'm basing from the advanced filtering tutorial from Microsoft. Some terms might be wrong (mistranslated), as I don't have an English version.

Final setup

This is my final setup, it's in a Portuguese version of Excel 2003. From left to right, the Data Range (A and B), the Criteria Range (C and D) and the filtered results (E and F), as well as an image of the Advanced Filtering Dialog. This will be slightly different due to the new GUI on Office 2007.

First of, you need to create a header for each column. It would look something like this.

Afterwards, go to Data > Advanced Filter. A menu should appear asking your action and your desired data range, criteria range and copy location. Select as action "Copy to somewhere else"

Your Data Range is your, well, data. In your case that would be $A:$C

Your Criteria Range is the range where you set the criteria by which you want to select the data. For this, create an accessory header and below of the C type NO.

Your Copy Range is where you want the filtered results to be. Just select the upper left corner cell.

After pressing OK, you now have a sorted table. However, this process is not automatic - you have to do it whenever you add new data.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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