2

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs. Extra tip: {Alpha!A2:B;Beta!A2:B;Charlie!A2:B} A semicolon ; combines vertically (in columns, one under ...


1

It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more). I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...


1

Assuming that B1 has a team name: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 20!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches '"&B1&"'") Please note the use of ' to enclose the team name and & to concatenate it to the SQL statement As the error message says, the ...


1

It's not possible by using IMPORTRANGE but you could achieve something similar by using Google Apps Script or and add-on. For details about extending Google Sheets please read https://developers.google.com/apps-script/guides/sheets Some hints The macro recorder and custom functions could not be used in this case Google Apps Script can't open a spreadsheet ...


1

Use IMPORTRANGE() to import data and VLOOKUP() to find match between Product Section. Here's a sample : First you need to import data from another sheet with IMPORTRANGE() You need to accept the access between sheets when you use IMPORTANGE: The best is to do this with a sample of every sheets to be sure you allow access for each one. To find match ...


1

The problem is that Column M and Column Nfrom the source spreadsheet include text and numbers / dates. Blank cells doesn't cause problems. In both columns, you should remove the text values before passing the imported values to QUERY. While it could apparently be "elegant" to have a single formula that does all the job, this makes the spreadsheet harder ...


1

As it was mentioned on the other answer Google doesn't offer a place that include a list of the connected spreadsheets but you could create automatically that list by using Google Apps Script. The access rights that the connection implies is read only and it's granted to the account that authorized the access. This connection will work while the authorizer ...


1

There is no built-in feature that list the spreadsheets that use IMPORTRANGE to import data from a specific spreadsheet. Considering that IMPORTRANGE to your spreadsheet could be added to any spreadsheet that any editor, viewer and commenter of your spreadsheet have access even if you don't have access to them, I think that there is no way to have a ...


1

Google Sheets hasn't a built-in function to make reference a Google Sheet by filename and a custom function can't be used because they ran anonymously and the Google Apps Script Google Drive and Google Drive Advanced services require authorization to use them. The only way is by using scripts triggered by other means like a macro, a custom menu, dialog or ...


1

Wrap each individual IMPORTRANGE call in IFERROR with a null array as the default length of a filled row if there is an error, like this: =QUERY({ IFERROR(Importrange(G3,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G4,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G5,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G6,"Sheet1!A2:D"),{"","","",""}...


1

OK, here is the formula I supplied: =IFS(B3="aaa","aaa",B3="nothing","",B3="myformula",ROUNDUP((1+2)/2),B3="myformula2",SUM(1+2)) It should be pretty easy to understand (i.e., condition, result, condition, result, etc.).


1

I had the same problem and removed the sheet protection and it started updating.


1

For users that want to do the same thing, but swap out the sheet name (instead of the cell name) they can use the following formula. =IMPORTRANGE("KEY", A4&"!E2:E") ...where A4 contains the name of the specific sheet you want to reference in your other spreadsheet and E2:E is the range that you want to import from that other spreadsheet.


1

As a workaround for the now() roadblock, you can change the settings of the spreadsheet to update every hour. File -> Spreadsheet Settings -> Calculation Update the sheet to recalculate itself on "On Change and every hour" or "On change and every minute". Though keep in mind, Selecting recalculation every minute may hang up the spreadsheet.


Only top voted, non community-wiki answers of a minimum length are eligible