Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

 
discard

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.