-1

I would be happy to get the help of the experts here…

Please check this spreadsheet: https://docs.google.com/spreadsheets/d/1pyog50iENw2p7X6g301gtLdh9TkWzCvvDa8-E-gQrqM/edit

Our company deals with buyers and sellers of eCommerce businesses. For each seller, we open a sheet where we manage the relevant tasks for his deal in the same way shown in the sheets “seller 1”, “seller 2” and “seller 3”. This way, we can easily see all the relevant tasks for this specific deal based on the relevant buyers that show interest in the business.

Here is where I need your help:

I want to create a sheet such as the “summary” sheet that will show me all the tasks we have for all sellers. In other words, in this sheet, the data of the tasks that are found in all the seller sheets will be shown. It is also important to mention that every few days, we may add a new seller / new sellers to this spreadsheet, so it is important for us to be able to update the summary sheet with ease once new sellers are added and without the need for lots of manual work.

Do you have any suggestions on the best way to achieve this goal?

player0
  • 124,011
  • 12
  • 67
  • 124
Omri
  • 7
  • 1

1 Answers1

1

try:

=ARRAYFORMULA(QUERY({SPLIT(
 {"Seller 1♥"&Seller1!A3:A; 
  "Seller 1♥"&Seller1!A3:A;
  "Seller 1♥"&Seller1!A3:A;
  "Seller 2♥"&Seller2!A3:A;
  "Seller 2♥"&Seller2!A3:A;
  "Seller 2♥"&Seller2!A3:A;
  "Seller 3♥"&Seller3!A3:A; 
  "Seller 3♥"&Seller3!A3:A;
  "Seller 3♥"&Seller3!A3:A}, "♥"), 
  {Seller1!B3:F;
   Seller1!G3:K;
   Seller1!L3:P;
   Seller2!B3:F;
   Seller2!G3:K;
   Seller2!L3:P;
   Seller3!B3:F;
   Seller3!G3:K;
   Seller3!L3:P}},
 "where Col3 is not null", 0))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks a lot for this solution! I have two more questions regarding your solution: A. Let's say that I want to add more sheets of sellers (seller 4, seller 5, seller 20, seller 50, etc.) - What I will need to add to this formula everytime I add a new seller? B. Let's say that I want to add more tasks in some/all sheets (task 4, task5, task6, etc.) and that the new tasks use the exact same 5 columns as I showed before. Do I need to change the formula everytime or is there a better solution? Thanks again! – Omri Aug 20 '20 at 22:13
  • @Omri in both cases all you need to do is to add new ranges – player0 Aug 20 '20 at 22:28
  • Thanks again for the reply, yes this is what I thought, just wanted to make sure I don't miss anything. One last question (if you have time of course): If I wanted to do things the other way, I mean to add all tasks to the summary sheet and then to import from this sheet to the relevant seller sheets automatically, is it going to make things harder or easier? I would be happy to learn how to do that if this is possible and you think it will make more sense. Thanks again! – Omri Aug 21 '20 at 06:18
  • while this would be possible I believe it would not help you because you wouldn't be able to edit seller sheets. the way you have it now is that in seller sheets you enter data and master sheets just gathers all those data under one roof tho you are not able to edit master sheet from master sheet – player0 Aug 21 '20 at 10:20