Looking for some help with your good skills.

For example: I setup three Google Sheets

1) Master Sheet (for Sync Function) 2) Company ABC 1 3) Company ABC 2

We always update MasterSheet for tasks and then we create SubSheets for each client but currently, we are updating each sheet for each client manually, but with your help, I would like to make this process bit automatic.

For example if you enter a new task and related details such as, date, company name, job type, job status in the Master Sheet, it should be reflected on Sub-Sheet which we have shared with the client.

Example: Company ABC 1 get its own data and ABC 2 and so on...

after watching a few videos I found that I can do this with =Query function but there is a small change I would like to make it a little bit easier.

So what I wanna do is I will have a locked cell on Sub-Sheet where I will dedicate a cell and in that cell if I put the company's name it should simply reflect data based on company name entered in that specific cell.

How can I do that? kindly help.

here is the Master Sheet link: https://docs.google.com/spreadsheets/d/187PR2oeFelq8qRsK_qY9Fodvedx8ChkvMZTh2Dtll9w/edit#gid=0

here is the ABC 1 Company Sub-Sheet https://docs.google.com/spreadsheets/d/1UsujYx9-tc_DEp4LLCbOISkC1mC0QLVyg0mN36MPhjE/edit#gid=0

here is the ABC 2 Company Sub-Sheet https://docs.google.com/spreadsheets/d/1AiTTKiVioNtEGPsYDS0xP_K0NpWvEeXmSQJOSVdfMDk/edit#gid=0

  • I read your question and even after looking at your sheets I still fail to understand what the issue is. The formula or the locking of the cell. Please clarify. May 26, 2020 at 18:47
  • Thanks alot for your reply and thank you for looking at the sheet for me. here i made a screenshot maybe it could help? prntscr.com/soajs5 Thanks a lot for your reply and thank you for looking at the sheet for me. here I made a screenshot maybe it could help? prntscr.com/soajs5 1) currently I added company name into the forums above but I want to keep a reference cell for entering the company 2) I added an Invoice link to a hyperlink in Master Sheet but on subsheet it is returning plain text without the link.
    – sub0
    May 26, 2020 at 19:00
  • here is the formula i am using =QUERY(importrange("187PR2oeFelq8qRsK_qY9Fodvedx8ChkvMZTh2Dtll9w","MasterDashboard!$A$2:$F"),"SELECT * WHERE Col2 = 'ABC1'",1)
    – sub0
    May 26, 2020 at 19:03
  • Please check your Sync 1 sheet May 26, 2020 at 19:21
  • i am inside ABC1 sheet but seems it not returning the data. can we check it now?
    – sub0
    May 26, 2020 at 19:39

1 Answer 1


Please use the following formula in your synced copied sheets.

=QUERY(IMPORTRANGE("xxxxxx","MasterDashboard!A2:F"), "select * where Col2= '"&B1&"' ")

(where xxxxxx is your Master sheet ID and B1 is your reference cell in the synced sheet)

Notes (for anyone using the formula)

  • When using IMPORTRANGE one has to use Col1, Col2 etc
  • To reference a cell, the syntax used is, pairs of single quotes ', double quotes " , ampersand &
    As an example: '"&TheCellHere&"'

Functions used:

  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Also check the answer in your "ABC 1 Company Sub-Sheet" May 26, 2020 at 22:00
  • thanks alot for the help. this is exactly what i wanted. much much appreciated. :)
    – sub0
    May 26, 2020 at 22:40
  • Thank you for taking the time to accept. Glad I could help :) May 26, 2020 at 22:43
  • what should be the formula if Col2 = company1 or company2
    – sub0
    Jul 19, 2021 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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