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 ...


2


1

If you want to 'skip a column', you can use regexreplace on one column of your range; so it will have the same number of rows. You would not be able to edit the column afterward obviously, but you can utilize some code in the final input for regexreplace function to drop-in what you need. =arrayformula({index(_yourRange,,1),regexreplace(index(_yourRange,,1)...


1

The problem is that Col5 has numbers but you are comparing it to a text value. Instead of Col5='14' use Col5=14 =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1er6drwogbR3-MAAYrtz96V1i_Cs0jTngcGYUeZ10rmA/edit#gid=1418684261", "A:Z"), "SELECT Col4 where Col5=14")


1

Basically there are two mistakes in your query formula. IMPORTRANGE("Sheet1","QC sheet!A792:B10000") can not return anything. The reason being that you need to replace the Sheet part of the formula with the sheet's ID that you want to import from. (More info about IMPORTRANGE) You mention Col4 when you import just 2 columns A and B from sheet!A792:...


1

You can use the entire url: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QDCf2MDkN7eopuTT-EieQI39Bpc_Y6D-3EWefimpk/edit#gid=0", "A:A") But just the spreadsheet id should also work: =IMPORTRANGE("1QDCf2MDkN7eopuTT-EieQI39Bpc_Y6D-3EWefimpk", "A:A") More generally the google sheets url is structured like this: https://docs.google.com/...


1

The following script is heavily adapted from an Audit Spreadsheet built by Ben Collins. The logic followed is: get the spreadsheet by url openByUrl(); though the script could just as easily have been bound to the 'IMPORTRANGE' spreadsheet get all the sheets getSheets()and loop through each them get the last row getLastRow(), and last column getLastColumn()...


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 ...


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