How to maintain absolute cell address even when deleting ranges?
you can freeze it like: =INDIRECT(“Responses!A2:R500”) instead of your: =ARRAYFORMULA(Responses!$A$2:R500)
google-sheets-formula
How to create INDIRECT array string of multiple sheet references in Google Sheets?
dudes who copy-pasted INDIRECT function into Google Sheets completely failed to understand the potential of it and therefore they made zero effort to improve upon it and cover the obvious logic which is crucial in this age of arrays. in other words, INDIRECT can’t intake more than one array: =INDIRECT(“Sheet1!A:B”; “Sheet2!A:B”) nor convert an arrayed … Read more
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
Number of columns in both sheets must be the same: Try ={Sheet1!A2:D10;Sheet2!A2:D10}
How to make a range repeat n-times in Google SpreadSheet
I would use split() function instead of arrayformula() and rept() function to repeat cell positions. For example, if your n=4 the formula will look like this: =split(rept(C1&”;”,4),”;”) rept() repeats cell position C1+semicolon four times creating a string and split() function divides created string by semicolons to horizontal cells. You can rotate resulted horizontal table to … Read more
Count cells that contain any text
You can pass “<>” (including the quotes) as the parameter for criteria. This basically says, as long as its not empty/blank, count it. I believe this is what you want. =COUNTIF(A1:A10, “<>”) Otherwise you can use CountA as Scott suggests
TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
The documentation for getSheetByName says, “Returns null if there is no sheet with the given name.” Therefore, you probably don’t have a sheet named “2020 MTD.” Create it or correct the name.
Col BY causing parse error in Google sheets query
BY column needs to be escaped with `: =QUERY(‘Applications received’!B:DL, “select B,CT,CU,CV,CW,CX,`BY` where B is not null order by CX”) https://developers.google.com/chart/interactive/docs/querylanguage#reserved-words
Google Sheet use Importxml error could not fetch url [duplicate]
You want to retrieve the price like 55,500₽ from the URL of https://tarkov-market.com/item/Pack_of_sugar and put to a cell on Google Spreadsheet. I could understand like this. If my understanding is correct, how about this answer? Issue and workaround: Unfortunately, IMPORTXML cannot be used for this situation. Because IMPORTXML is used like =IMPORTXML(“https://tarkov-market.com/item/Pack_of_sugar”,”//*”), an error like … Read more
How to create all possible pair combinations without duplicates in Google Sheets?
google-sheets It is a hard task for native functions. Try a script and use it as a custom function: function getTournament(teams_from_range) { // teams_from_range — 2D Array var teams = []; // convert to list teams_from_range.forEach(function(row) { row.forEach(function(cell) { teams.push(cell); } ); } ); return getTournament_(teams); } function getTournament_(teams) { var start = 0; var … Read more
Google Sheets Formula for Extracting Domain From Website?
try: =ARRAYFORMULA(INDEX(SPLIT(REGEXREPLACE(A8:A12, “https?://www.|https?://|www.”, ), “/”),,1)) UPDATE 1: =ARRAYFORMULA(IFNA(REGEXEXTRACT(INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1), “\.(.+\..+)”), INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1))) UPDATE 2: =INDEX(IFERROR(REGEXEXTRACT(A1:A, “^(?:https?:\/\/)?(?:ftp:\/\/)?(?:www\.)?([^\/]+)”)))