CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
PKHunter
PKHunter

Reputation: 707

Combing base data set for a formula from two *other* Google sheets

We have hit the dreaded 5 million rows limit which is so small for any semi-serious data.

We have an important ArrayFormula piece in one of our worksheets (tab) currently that summarizes the data from another worksheet in the same file where time series data is kept with dates. This is our current function:

=ArrayFormula(SUMIFS(DataSheet!$B:$B, 
          MONTH(DataSheet!$A:$A), 1, 
          YEAR(DataSheet!$A:$A), 2020)
)

Explanation: This basically summed all of column B in the DataSheet tab for the month of Jan 2020 based on date found in column A of that sheet.

However, this worksheet of data that is now running close to that row limit. We can move it to another Google Sheets file, and refer to the same data via IMPORTRANGE.

The question then is how to refer to that data instead of the DataSheet!$A:$A in the above old formula? Will this reference be replaced by the entire IMPORTRANGE function?

Old:

=ArrayFormula(SUMIFS(DataSheet!$B:$B, 
          MONTH(DataSheet!$A:$A), 1, 
          YEAR(DataSheet!$A:$A), 2020)
)

New:

=ArrayFormula(SUMIFS(IMPORTRANGE(filename, rows)!$B:$B, 
          MONTH(IMPORTRANGE(filename, rows)!$A:$A, 1, 
          YEAR(IMPORTRANGE(filename, rows)!$A:$A, 2020)
)

This does not work of course, because we cannot have the exclamation ! followed by the column in an importrange. Any other thoughts?

Upvotes: 0

Views: 106

Answers (2)

Iamblichus
Iamblichus

Reputation: 19309

You want to IMPORTRANGE from two different sheets in a different spreadsheet.

While the following formula will import data from both sheets, it will also import the blank rows, so you might have to scroll down hundreds of rows in order to see the data from the second sheet (and this might give your the wrong impression that the second sheet is not getting imported):

{
  IMPORTRANGE("SPREADSHEET_ID","CurrentMonth!$A:$J");
  IMPORTRANGE("SPREADSHEET_ID","All2020!$A:$J")
}

You can use QUERY in order to filter out blank rows:

=QUERY(
  {
   IMPORTRANGE("SPREADSHEET_ID","CurrentMonth!$A:$J");
   IMPORTRANGE("SPREADSHEET_ID","All2020!$A:$J")
  },
  "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1 DESC"
)

enter image description here

Note:

  • I thought you'd like to sort the data according to the date in column A, please remove ORDER BY Col1 DESC if that's not the case.

Upvotes: 1

MattKing
MattKing

Reputation: 7773

Try this in cell A1 on a fresh, brand new tab somewhere:

=ARRAYFORMULA(QUERY(1*TEXT(IMPORTRANGE("[spreadsheet key]","Sheet1!A:B"),{"mmmyyyy","0.00"}),"select Col1,SUM(Col2) where Col2<>0 group by Col1 order by Col1")

The "spreadsheet key" is the combination of letters and numbers after the "/d/" and before the "/edit..." in the URL of your source sheet. Obviously, you'd also replace "Sheet1!A:B" with whatever the real tab/column reference is.

Then, select all of Column A and from the Menu choose Format>Number>More Formats>Custom Number Formatting, Then this in the dialog box:

mmmm yyyy

Upvotes: 1

Related Questions