automatically duplicate/cop data from a shared worksheet to my own

How about this:

Automatically Pull Data from 'TEAM' to 'PERSONAL' Workbook

1. Use Google Sheets' IMPORTRANGE Function:

• This function allows you to import a range of cells from another spreadsheet.
• Syntax: IMPORTRANGE(spreadsheet_url, range_string)

2. Filter Rows Based on Column 'A':

• Use the FILTERfunction to pull only the rows where column 'A' contains 'JC'.
• Syntax: FILTER(range, condition)

Example Steps:

1. Get the URL of the 'TEAM' Workbook:

• Open your 'TEAM' workbook and copy the URL.

2. Set Up IMPORTRANGE in 'PERSONAL' Workbook:

• In your 'PERSONAL' workbook, use the following formula to import data:

  =IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:Z")

   

• Replace "URL_of_TEAM_Workbook"with the actual URL of your 'TEAM' workbook.
• Replace "Sheet1!A:Z"with the actual range you want to import.

3. Filter Rows with 'JC' in Column 'A':

• Use the FILTERfunction to get only the rows with 'JC':

  • =FILTER(IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:Z"), INDEX(IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:A"), 0) = "JC")

     

Reflect Changes in Real-Time

• Google Sheets automatically updatesthe data from the source workbook, so any changes in the 'TEAM' workbook will reflect in your 'PERSONAL' workbook.

Copy Entire Sheet and Filter Rows

If you prefer to copy the entire sheet and then filter out rows, you can use a combination of IMPORTRANGE and QUERY functions:

1. Import Entire Sheet:

   =IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:Z")

    

2. Filter Rows Using QUERY:

   =QUERY(IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:Z"), "SELECT * WHERE Col1 = 'JC'")

    

Editing in 'PERSONAL' Workbook

• Manual Copy-Paste: If you make edits in your 'PERSONAL' workbook, you can manually copy and paste the rows back into the 'TEAM' workbook.