Multiple Google Sheet dependancy in master google sheet with conditions

Question

I have googled a lot for this but not getting how to do it.

I am having 3 companies which provide us the stocks, let's assume Company1, Company2, and Company3 in our Google Sheets.

Once we add the orders it will be reflected in the respective companies Google Sheets orders. It is not mandatory that all company will see all orders because they may be dealing with particular items.

Now each and every company google sheet has Stock Available dropdown with (Yes/No) options where they can check for that particular order id whether it feasible or not.

My requirement: is if any of the company updates Stock Available to 'Yes' than that company name must be appended in the Master Sheet which is linked to those Google Sheets with other companies. How can I achieve this with Google Sheets?

Eg: Since the OrderId -> 1 is marked Yes by Company1 its entered in Master Sheet, Similarly OrderId -> 2 is marked Yes by Company1, Company2 and Company3 hence it is added by comma separated values in Master Sheet.

NOTE: Stock Available column is a dropdown with values (Yes / No)

Company1
========
OrderId         Order Details          Stock Available 
---------------------------------------------------
1               Bolts                   No 
2               Screws                  Yes
3               Resin Type A            Yes

Company2
========
OrderId         Order Details          Stock Available 
---------------------------------------------------
1               Bolts                   Yes
2               Screws                  Yes
3               Resin Type A            Yes


Company3
========
OrderId         Order Details          Stock Available 
---------------------------------------------------
1               Bolts                   No 
2               Screws                  Yes
3               Resin Type A            No

Master Sheet
------------

OrderId         Order Details          Companies
------------------------------------------------
1               Bolts                   Company1
2               Screws                  Company1, Company2, Company3
3               Resin Type A            Company1, Company2

Rouven B. Rouven B. · Accepted Answer · 2018-10-11T11:06:37

This formula should work:

=TEXTJOIN(", "; TRUE; IF('Company 1'!C2="Yes"; "Company 1"; ""); IF('Company 2'!C2="Yes"; "Company 2"; ""); IF('Company 3'!C2="Yes"; "Company 3"; ""))

Here is a demo: https://docs.google.com/spreadsheets/d/1rqkuNevA_SqpD6r5lpfuKfbXn286vzhf1HlFGardLvs/edit?usp=sharing

EDIT:
To explain this a bit:

IF('Company 1'!C2="Yes"; "Company 1"; "")

Checks if the value in cell C2 in sheet Company 1 equals "Yes". If so, it returns "Company 1", else the empty sting (""). I do this for all three company sheets.
I than wrap it all in

=TEXTJOIN(", "; TRUE; …)

This will join all entered strings with a ", " in between. The second argument (TRUE) tells the function to ignore empty strings.

Multiple Google Sheet dependancy in master google sheet with conditions

1 Answers