google sheets – Arrayformula with iferror and vlookup is not automatically updated

My spreadsheet is to verify inventory. I use several of arrayformula, iferror and vlookup.
The problem is that I just noticed that sometimes the formula is not updated. It could be because I duplicated the Google sheet in a new one so that it works for another month.
Here is my formula.


When it is not updated, leave the entire column blank. And make my inventory count badly. I can have it updated by manually going to the cell and pressing enter. But that is not what I want to do. There are about 100 cells that I have to go through.

And setting the spreadsheet to update every minute does not solve this problem.

google sheets – ArrayFormula to extract part of the chain

I have found the following formula, and I find it quite useful, I want to turn it into a matrix shortly, but before doing so, I was wondering if there would be an easier way to do it.

= if (len (LEFT (MID (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("37", MID (A2, SEARCH ("02", A2) +2,
LEN (A2))) - 1)) = 14, LEFT (MID (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("37", MID (A2, SEARCH ("02 ", A2) +2,
LEN (A2))) - 1), if (len (LEFT (MID (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("15", MID (A2, SEARCH (" 02 ", A2) +2,
LEN (A2))) - 1)) = 14, LEFT (MIDDLE (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("15", MEDIUM (A2, SEARCH ("02 ", A2) +2,
LEN (A2))) - 1), if (len (LEFT (MID (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("10", MID (A2, SEARCH (" 02 ", A2) +2,
LEN (A2))) - 1)) = 14, LEFT (MID (A2, SEARCH ("02", A2) + 2, LEN (A2)), SEARCH ("10", MID (A2, SEARCH ("02 ", A2) +2,
LEN (A2))) - 1), "")))

Google document to illustrate:

google sheets: omit arrayformula if the result of that formula is blank

I have a matrix formula that looks like this:

= ArrayFormula (IF (COUNTIF (B2: B, QUERY (& # 39;! Answers & # 39 ;! B2: G, "select G where B! = & # 39; & # 39;")) = 0, IFERROR ( QUERY (& # 39; Form Respes & # 39;! B2: G, "select B, G where B! = & # 39; & # 39;"), "")))

Retrieve data from a different sheet that is connected to Google Forms.
With the first YES Y The country I try to verify if column G of the answer sheet of the form is already present in column B of my current sheet.
If that is not the case, I ask for columns B and G.

However, if the value is Already present, I do not want to consult it, but I do not want an empty line, which is now happening because of "" as the third argument for the YES declaration.

Is there any way I can only check if there is not a duplicate, without adding an empty line?

Google Sheets – ArrayFormula stops working after shuffling rows

The only solution is to temporarily disable Arrayormula by eliminating = sign or insert & # 39; in front of = sign – then do your row swap and then re-arrange your matrix formula.

The second solution would be to remove the static value after moving the row so that the matrix formula can be displayed instead of being published. #REFEREE! error.

The third option includes a script that will eliminate the given range (s).

function moveValuesOnly () {var ss = SpreadsheetApp.getActiveSpreadsheet ();
var source = ss.getRange (& # 39; Sheet1! B10 & # 39;);
source.copyTo (ss.getRange (& # 39; Sheet1! AO3: AO & # 39;),
{contentsOnly: true}); }


clearArrays () {function
var sheet = SpreadsheetApp.getActive (). getSheetByName (& # 39; Sheet1 & # 39;);
var rangeToClear = ['AO3:AO', 'AR4:AR8'];
for (var i = 0; i <rangeToClear.length; i ++) {
sheet.getRange (rangeToClear[i]) .clearContent ();

Google Sheets: use of ARRAYFORMULA and QUERY to produce results in several columns

I would like to be able to group rows based on unique values ​​and then generate output AVERAGE Y STDEV values ​​of two different variables for those groups.

Using the table below as an example, I would like to determine the AVERAGE Y STDEV of two different variables for each unique ID and type (1a, 1b, 2a, 2b, 3a, 3b).

I received help on this previous problem that combined QUERY Y Arrayormula and it worked great, but that was only for one variable. Perform functions (AVERAGE, STDEV) in rows grouped by unique values ​​in Google Sheets.

Using the same formula twice adds additional unique value columns. This not only adds additional clutter, it also seems that it could introduce additional errors if the unique ID columns do not match or are not aligned. If possible, I would like to make sure that the calculations are drawn from the same set of unique IDs. Using the table below, the desired output would produce a column for the unique IDs and then the columns for "AverageLength" "LengthSD" "AverageWidth" "WidthSD".

ID type L W
1 to 5 9
1 to 7 8
1 to 9 3
1 b 2 4
1 b 4 1
1 to 6 3
1 to 9 8
1 b 8 5
1 to 1 2
2 to 5 2
2 to 2 1
2 to 4 7
2 b 6 6
2 b 7 5
2 to 8 9
3 b 1 2
3 b 7 4
3 to 5 3
3 to 6 6
3 to 8 9
3 to 9 3
3 b 4 5
3 b 6 4
3 b 3 1```

ARRAYFORMULA + QUERY + IMPORTRANGE – The final cell reference in IMPORTRANGE is acting as if it were absolute when I need it to be relative

I know I'm stringing together a lot here, but it's VERY functional. Here is my formula with the address of the modified document for privacy.

= ARRAYFORMULA (IF (ISBLANK ($ AE $ 3: $ AE), (query (importrange)) A1000: H ")," select Col2, Col6 Where Col1 = & # 39; "& (& # 39; Bottles of 14 oz & # 39;$ G3: $ G) And "& # 39;"))))

In summary, I want this formula to be automatically filled out in a sheet that collects answers for a Google form. It references another sheet and returns data according to the value of column G in my current sheet. The only problem is what is bold above. This function ONLY returns the value that matches $ G $ 3 in my current sheet, acting as if it were an absolute cell reference. I want it to be relative so that it fills each line according to the value that is in cell G of that row.

I would be grateful for any idea!

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

formulas – How to use ARRAYFORMULA and UNIQUE together in Google Sheets

How can I expand the UNIQUE function using ARRAYFORMULA?

Here is my data set, you can also find it here.
I want to see the unique values ​​of each row in the following columns.

CL1 | CL2 | CL3 | CL4
Jan | FEB | FEB | JAN >> JAN | FEB
Mar | Mar | June | AUG >> MAR | June | AUG
SEP | OCT | June | MAR >> SEP | OCT | June | SEA

ARRAYFORMULA and UNIQUE together in Google Sheets

I used the following formula in cell E2:


But, the problem is that I need to copy the exact formula in the following rows. While, I would prefer to use ARRAYFORMULA.

The problem is that, since ARRAYFORMULA is also using the data range, as UNIQUE, there is a conflict.
I tried the following but it did not work:


In other words, I want exactly the same result but using ARRAYFORMULA instead of manually entering the formula for each row.

Any other solution would be appreciated.

google sheets: find the maximum value of the column after the individual operation in each value of that column using arrayformula or similar

I have a Google sheet with a column D with identifiers in a certain format (ie, Y19_XXX):


Now I want to determine the highest XXX value in that column that is less than 800 (here in this example, therefore, 234).

I tried: = MAXIFS (ARRAYFORMULA (VALUE (RIGHT (D3: D300,3))), ARRAYFORMULA (VALUE (RIGHT (D3: D300,3))), "<800"), but the MAXIFS formula complains that it needs a range to operate, while I thought that the ARRAYFORMULA (VALUE (RIGHT (D3: D300,3))) The formula resulted in a range.

Someone an idea? Thank you 🙂

Google Sheets: How to use arrayformula to search and get data from only a certain dynamic set of columns?

I have a table where my headers are generated dynamically from another source and can appear with any header value, in any order.

In the following example I have the header values:

AA - BB - CC - ABBA - KK - VV

but it could have been:

CC - AA - QQ - YY

I do not know in advance what headings I will get, or how many.

My problem is that I want to add all the values ​​of the product, using a matrix formula, where the column headers include, for example, "Bed and breakfast"

So basically = arrayformula (sumifs (C4: H1000, $ C $ 3: $ H $ 3, "* BB *")), if that had been one thing.

My closest estimate is that I need to use a VLOOKUP of some kind, but since the index of the columns to get the data is not static, I can not figure out how to solve it.

Desperate for help

enter the description of the image here