1

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. – marikamitsos May 26 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 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 at 19:03
  • Please check your Sync 1 sheet – marikamitsos May 26 at 19:21
  • i am inside ABC1 sheet but seems it not returning the data. can we check it now? – sub0 May 26 at 19:39
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:

| improve this answer | |
  • 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" – marikamitsos May 26 at 22:00
  • thanks alot for the help. this is exactly what i wanted. much much appreciated. :) – sub0 May 26 at 22:40
  • Thank you for taking the time to accept. Glad I could help :) – marikamitsos May 26 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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