1

The title may be a little too ambiguous but I'll try to give a rundown as best I can;

I have a Google sheet with multiple "Sheets" or Tabs (as I'd like to call them) within it.

The Main tab is blank and the rest have various cells with different dates on them. The dates that are close turn their respective cells Red. I have existing conditional formatting to do this.

What I want to achieve is the main tab basically checks all the other tabs for any red cells and pops them into the main sheet in a vertical column.

Here's an example:

"Second tab"

  • (A1) Date 1 (green)

  • (A2) Date 2 (red)

  • (A3) Date 3 (red)

  • (A4) Date 4 (Green)

"Main tab"

  • (A1) Date 2 (red)

  • (A2) Date 3 (red)

As you can see, "Main tab" ignores any green cells from "Second tab" and only grabs the red ones. In my case though, there are many more tabs, not just 1.

I have a feeling =IMPORTRANGE may help in this scenario? Maybe.

Any help is greatly appreciated!

0

You don't need IMPORTRANGE. Also, you can't have a formula that checks only for color (you would need a script or addon for that). However, you mention that you already have conditional formatting rules in place that already do their job to turn cells red so you can gain an advantage of it and use the same rules in your Main sheet (but not as CF) to get data you need.


  • paste in B2 cell:

=QUERY({
 Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G; 
 Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I; 
 Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F}, 
 "where Col1 < date '"&TEXT(TODAY(), "yyyy-mm-dd")&"' 
    and Col1 > date '"&TEXT(TODAY()-99999, "yyyy-mm-dd")&"'", 0)

  • paste in C2 cell:

=QUERY({
 Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G; 
 Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I; 
 Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F}, 
 "where Col1 > date '"&TEXT(TODAY(), "yyyy-mm-dd")&"' 
    and Col1 < date '"&TEXT(TODAY()+30, "yyyy-mm-dd")&"'", 0)

  • paste in D2 cell:

=QUERY({
 Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G; 
 Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I; 
 Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F}, 
 "where Col1 > date '"&TEXT(TODAY()+29, "yyyy-mm-dd")&"' 
    and Col1 < date '"&TEXT(TODAY()+61, "yyyy-mm-dd")&"'", 0)

0


  • if you want to get rid of duplicates wrap each query into UNIQUE
  • Yeah I'm not sure as to how I can achieve that. I have a formula that looks over a range of cells and checks what date they are and applies the correct coloring. I'm not sure how using that can help me populate a shortlist on the main tab. Some importing will need to take place surely? – Jay Jun 13 at 22:57
  • @Jay can you share a copy of your sheet? – user0 Jun 13 at 22:59
  • @Jay answer updated – user0 Jun 13 at 23:58
  • 1
    Thank you this works perfectly. I'm going to figure out what you've done. Thanks again! – Jay Jun 14 at 0:07

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.