=Query Function from another sheet based on a cell Value

Question

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

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:

• QUERY
• IMPORTRANGE