Show rows with specific text in a column in another file.

[Excelforum*ser_mH7]

How to show rows with specific text in a column in another file.

Example:I want all rows that have A in 4th column to be copied to another file.
I want it to happen actively all changes are synced immediately not just once.

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Excelforum*ser_mH7]

Example: .

[Pete_UK]

In the attached file I've added this formula to D2 in Sheet1:

=IF(C2="","-",C2&"_"&COUNTIF(C$2:C2,C2))

and copied it down to beyond your data (as the hyphens help to show), in order to accommodate more data being added. The formula identifies the team for each record, and gives each a unique sequential record (or ID).

I've also set up a sheet for Team A, with this formula in A2:

=IFERROR(MATCH($A$1&"_"&ROWS($1:1),Sheet1!$D:$D,0),"")

Note that the name of the team is in cell A1, and this formula finds where each record (Team A_1, Team A_2, Team A _3 and so on), exists in Sheet1, i.e. it gives the row numbers where each successive record can be found.

The following formula is in B2:

=IF($A2="","",IF(INDEX(Sheet1!A:A,$A2)="","",INDEX(Sheet1!A:A,$A2)))

and this can be copied into C2, and then all three formulae can be copied down as far as you like (I've copied to row 10 in the file). Once the sheet for Team A has been set up, you can just copy the sheet (use Ctrl-drag), rename the new sheet, and put the team name in cell A1. You will find that any changes made to Sheet1 will be reflected in the appropriate sheet(s) immediately.

Hope this helps.

Pete

[Excelforum*ser_mH7]

Thanks that helped a lot @Pete_UK,but..
Is it possible to copy to another file.If not then
Is it possible to share this file with other accounts using Google Drive and block access to spreadsheets other than their assigned work.

[Pete_UK]

This is an Excel forum, so I can't help you with any queries about Google Sheets as I have never used it.

Regarding copying to another file, the easiest way to achieve this is to arrange the file window to occupy only some of the space of the Excel window (press the middle button (restore) on the file window, and adjust the position of the bottom corner), so that you have space around the file window. Then MOVE the sheet tab for the TEAM_A sheet (i.e. just drag it) into the blank space around the file window. That will set up another file window and should adjust the formulae so that they point back to the original file. Then you can copy the TEAM_A sheet within that file window and rename it and change the value in cell A1, as described above. Use File | Save As to save the newer file with a different name.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).