Google sheets: the query function does not put the consolidated data in the right place

Instead of , in your formula, use ; – This will stack the queries one below the other:

= {QUERY (Sheet1! A: B); QUERY (Sheet 2! A: B)}

Also, you may want to wrap everything outdoors QUERY to get rid of the empty rows:

= QUERY ({QUERY (Sheet1! A: B); QUERY (Sheet2! A: B)}, "where Col1 is not null", 0)

(for local use of the EU instead of ;)

Create & # 39; markers & # 39; in google sheets

I'm pushing the edge of Google's sheets, doing with a spreadsheet what to do in a database.

As an example: Cust_Orders is a sheet divided into blocks of 15 rows. Each block is an order form with contact information and 14 lines of articles.

A column is the status of the order, with the possible values ​​of Budget, Order, Cancel, Done, and Blank.

I would like to place a button in the frozen header area that would scroll the sheet until the first occurrence of the word & # 39; Blank & # 39;

Ideally, I would like to do this without opening the box & # 39; search & # 39;.

Generalizing this: A cell or two adjacent cells with content and a button. When you click on the button you will find the first appearance of the content. In effect, it becomes a storage point for frequently used search commands and a navigation method for large sheets.

Query Google sheets: How to use IMPORTRANGE when I have multiple tabs?

I have to export some data from a Sheet with 30 tabs (one tab for each day of the months) and I have to extract the data according to specific criteria of all the sheets that must be done every month on a new sheet.

Is there any way to make the formula automatically search all the tabs instead of manually typing 30 IMPORTRANGE in a QUERY function?

Example for the formula I created for a day of the month:

= QUERY ({IMPORTRANGE ("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", "01.05.2019! A1: M200")}, "SELECT Col2 WHERE Col7 contains & # 39; Emil & # 39; and Col11 <> 10")

Your help will be highly appreciated.


formulas – Rotate and duplicate the values ​​of the rows in the Google Sheets

I'm trying to rotate the values ​​on a sheet and then duplicate the first value in the row as a header. For example:

a | b | c | re

it would become:

a | second
a | do
a | re

Is there any way to do this only with the Google Sheets commands, or do I need to use a script?

google sheets – join two work formulas

I have the following work formula

Located in K2 = IF (INDEX (Details! B18: B81, MATCH (C2, C2,0)) <> C2, S30, "0")

So I want to use only the number in S30 if the date in C2 is not in a range of dates in the details page within B18: B81

Now I need to use this formula in the cells below, however, there is an additional verification that must be done

This working formula is

Located in K3 = IF (AND (C3 <HOY () + 1, J2 <> ""), K2 + S $ 30,)

Therefore, I want the above formula to do what it does if the date in C3 is not within the range on the Details page within B18: B81 (the date range is Sunday or the days closed)

If C3 is in the range, I want you to add zero instead of the S30 number.

The attached image gives you the cells that are referenced and for this formula, S30 equals 2

enter the description of the image here

Microsoft Excel – Dot leaders in Google sheets

Point leaders are repeated points that visually connect a label to the corresponding information on the same line, commonly used in tables of contents but also in accounting formats, for example:

Income ............. $ 100,000
Expenses ........... $ 80,000
Net Income ......... $ 20,000

In Excel, you can add point leaders to any cell as follows:

  1. Right click on the cell and select Cell format….
  2. Select the Number tongue.
  3. Under Category, select Personalized.
  4. At Kind field, entrance @ *..
  5. Click IN AGREEMENT.

After the contents of the cell (represented by the @ at Kind entry field), this will show a space followed, but no matter how many periods it takes to fill the cell, that is, a point leader.

Apart from the obvious way to just manually put a lot of points at the end of each cell, is there a way to do the same in the Google sheets?

Google Sheets – Selection of a total for the current month

I have this working formula (below)

"select *", 0),
"select Col2
where Col3 matches "& WEEKNUM (TODAY (), 16) &" & # 39; ", 0))

What do I need to change to get my Total for the current month?

Google Sheets: How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I am learning a new language and I recently discovered Google Sheets & # 39; GOOGLE TRANSLATOR function. I thought it would be good to have a personal dictionary of translations that I can even write down. So I created a simple sheet that translates words from column A into column B. So far so good.

The problem is that I can not tell Google Sheets to "translate every cell from column A to column B". used to Arrayormula but it does not work as expected. = ArrayFormula (GOOGLETRANSLATE (A2: A, "en", "de")) only results for

Only B2 is translated, not everything else.

As you can see, only B2 is translated, not everything else.

FWIW, dragging the fill driver down still works, but I want an endless dictionary of such translations. I created the sheet in the web application, but I use it mainly through the Android application. I just want to write the word in column A and get its translation. Unfortunately, if the filler driver works even on mobile devices, I do not know how to activate it. (And I know this forum is for web applications, so I'm going to stop there).

How to activate a script on Google sheets when a value changes

I try to trigger a script when I change a value in a certain cell or range of cells.
(It has not been modified by the user, so I can not use onEdit)

I tried using onChange, but this does not activate my script at all

function onChange () {
// verwijder verwerkte & # 39; klaar & # 39; regels

var actieSheet = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("Actie");
var asRange = actieSheet.getRange ("F3: F25")
var asValues ​​= asRange.getValues ​​();
for (var i = 0; i <22; i ++) {
x1 = & # 39; false & # 39 ;;
asValues[i][0]= false;
actieSheet.getRange ("F3: F25") .setValue (asValues);

Is there a way to activate a script if you change a cell or even if there is a change in a sheet, will it do it for me?

formulas: how to import / combine data from 2 separate sheets of another spreadsheet

I need to import data from "Worksheet A" to "Worksheet B", but all data is on two separate sheets "MI" and "NY" in "Worksheet A"

Basically I need exactly this but with a function to import from another spreadsheet, not just within it

= QUERY ({{ARRAYFORMULA (ROW (INDIRECT ("A1: A" & COUNTA (QUERY (MY! A2: A; select A where A is not null ")))))  QUERY (MY! A2: A;" select A where A is not null ")}; {ARRAYFORMULA (ROW (INDIRECT (" A1: A "& COUNTA (QUERY (NY! A2: A;" A where A is not null ")))))  QUERY ( NY! A2: A; "select A where A is not null")}}; "select Col2 order by Col1")