New answers tagged

0 votes
Accepted

Fill a column with dates for two days per week

To get Mondays and Thursdays in the next 4 weeks starting on 2023-10-02, use this: =let( dates, sequence(4 * 7, 1, "2023-10-02"), filter( to_date(dates), match(weekday(dates, ...
doubleunary's user avatar
  • 12.6k
0 votes

FLATTEN data from SPLIT without helper columns

TOCOL Google Sheets has now added the TOCOL function which transforms an array or range into a single column. Using TOCOL instead of FLATTEN greatly reduces the length of this formula because it can ...
Blind Spots's user avatar
  • 2,984
1 vote

Adding (and updating) time stamp when cell color is changed in Google Sheets

The execution log of the script project contains this error: Error: Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet. The error ...
doubleunary's user avatar
  • 12.6k
1 vote

Google Sheets, how to sort query results manually (Not in query)

Keep your primary sheet as a main "level 1" sorting logic and querying sheet. Use ORDER BY in your first sheet. Hide your first sheet. Then pull your data to a second sheet with a simple ...
Demir Aydemir's user avatar
0 votes

Clear cell value if cell in adjacent column is blank

A Jedi’s strength flows from the documentation Delete data from cells it may not; only return empty or filled values, Google Sheets can. 1 This is the way 2 1 Yoda      2 Din Djarin ...
Blind Spots's user avatar
  • 2,984
0 votes

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Basic Formula =SPLIT(A2, ";") Will SPLIT the string in A2 into a new column at each ; Multi-Row Formula =BYROW(A2:A6, LAMBDA(r, SPLIT(r, ";"))) Will SPLIT the strings in ...
Blind Spots's user avatar
  • 2,984
1 vote

Looking up two criteria in a table, one vertical, one horizontal and finding a value

Basic Formula Works for a single date/row. =INDEX(range, MATCH(name, nameCol, 0), MATCH(date, dateRow, 0)) Error Handling Add the IFNA function to hide errors when no value is found. =IFNA(...
Blind Spots's user avatar
  • 2,984
0 votes

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

This sounds like my question, but I admit to not understanding the answers given. I had selected and copied ctrl-c about 20 lines from a Google calendar invite - a list of procedure steps. I wanted ...
BrentHarsh's user avatar
0 votes

Alternate to nested if statements (alternate posing of question)

My answer is a variation on @erik-tyler's answer Using FILTER =JOIN(" ", INDEX(FILTER({C2:T2; E2:V2}, 1/(1/E3:V3)>0),,1)) Your approach to organizing data works against you. You could ...
Blind Spots's user avatar
  • 2,984
3 votes

Insert space after every character of a text string

I solved my problem with the following formula: =TRIM( REGEXREPLACE( A1, "(.)", "$1 ")) Credit to @Erik Tyler for suggesting the TRIM function which provided a much ...
Patrick Hennessey's user avatar
0 votes

Formula that looks for any matching substrings between two cells

@doubleunary's answer is a good one. The approach below uses SPLIT to convert the individual cells into arrays of substrings Substrings to Arrays =LEN( FILTER( SPLIT(A1," "), ...
Blind Spots's user avatar
  • 2,984
2 votes
Accepted

Formula that looks for any matching substrings between two cells

One way to do that is to create a regular expression, like this: =regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|")) When B1 is foo bar, the ...
doubleunary's user avatar
  • 12.6k
0 votes

Formula to autofill value from a list on a separate sheet

The accepted answer works well. I show a couple of alternatives below as well as VLOOKUP Single-Formula which extends the approach in the accepted answer to calculate the results for multiple rows. ...
Blind Spots's user avatar
  • 2,984
1 vote

Making a bubble chart in Goggle Sheets for year-on-year comparison - ie year versus month?

As I've indicated in the comments, I don't think you can easily get a bubble chart that meets the requirements in the picture because of scaling issues, however to get the general layout requested, ...
The God of Biscuits's user avatar
1 vote
Accepted

Google Sheets complex formula or script is needed

Use rows() and filter(), like this: =if( and( rows(filter(B4:B6, len(A4:A6))) = rows(filter(B4:B6, B4:B6 >= 100%)), rows(filter(B8:B10, len(A8:A10))) = rows(filter(B8:B10, regexmatch(...
doubleunary's user avatar
  • 12.6k
2 votes

XPath Query to Retrieve Element Value and Name

Things to have in mind about using xPath on IMPORTXML It supports xPath 1.0. An old version of the IMPORTXML help article mentioned the supported version; the current version points to a third-party ...
Rubén's user avatar
  • 43.9k
2 votes
Accepted

XPath Query to Retrieve Element Value and Name

Alternatives Here are alternative approaches to get the data in your preferred layout: IMPORTDATA =IMPORTDATA(url, delimiter) Imports from a URL in CSV or TSV format. =LET(url, "https://home....
Blind Spots's user avatar
  • 2,984
0 votes
Accepted

onEdit updates correctly, but then overwrites following columns after pasting (editing) multiple rows

The Range.offset() function returns a range that is the same size as the original range, rather than a single cell or column, unless you specify optional parameters. You can specify those optional ...
doubleunary's user avatar
  • 12.6k
2 votes
Accepted

I have multiple unequally sized rows that I want to combine into a single matrix. How can this be done?

You could try something as simple as =tocol(code_f3!A1:ZZZ,1) to put all the results (minus blanks) into a single column. ZZZ is the maximum number of columns Google Sheets allows (18278).
The God of Biscuits's user avatar
1 vote

How can I find the first empty cell in a row?

Needed a formula that could figure out the sum of a list of numbers in the same column below the current cell up to the next blank cell, came up with this, I know this is an old post but I hope it ...
Andy C's user avatar
  • 11
0 votes

How can I retrieve a 2D array by its header?

Use filter(), like this: =filter(B2:D, A2:A = E2) See filter().
doubleunary's user avatar
  • 12.6k
1 vote

Excel style table in Google Sheets

To implement something like Excel's Structured References, try this: The following Apps Script will add a function to created Named Ranges in the format SheetName_ColumnHeader for the contiguous range ...
volfied's user avatar
  • 21
0 votes
Accepted

Runner's time generates error when divided by distance run regardless of the number format

Rubén is correct you are entering values that don't match a valid number format so Sheets is interpreting them as text. When you then perform your division calculation on the text Sheets tries again ...
Blind Spots's user avatar
  • 2,984
0 votes

Stuck with a google sheets IF formula for generating boolean strings

Use filter() and join(), like this: =join(" OR ", filter("""" & B8:B & """", len(B8:B))) See filter() and join().
doubleunary's user avatar
  • 12.6k
0 votes

Google Form Information and Google Sheets

Choose Insert > Sheet and copy form responses to the new sheet with an { array expression } like this: ={ 'Form Responses 1'!A1:N } You can then use plain vanilla spreadsheet formulas in the new ...
doubleunary's user avatar
  • 12.6k
1 vote

Runner's time generates error when divided by distance run regardless of the number format

Enter 00:09:07.7 as in Google Sheets, duration values should include hours. You might also have to set the cell format to duration as Google Sheets might show durations as time by default.
Rubén's user avatar
  • 43.9k
0 votes

How to specify the entire sheet as range in Google Sheets?

=CELL("address",Sheet2!A:A)&":"&ROWS(Sheet2!A:A) references Sheet2 as a whole. It returns a string like Sheet2!$A$1:1000 . This means address of Sheet2 all rows (1000 in ...
vstepaniuk's user avatar
1 vote
Accepted

How can I query columns instead of rows in a =QUERY?

One option is to use TRANSPOSE. This function change columns to rows. NOTE: When feeding QUERY with an array of values, like the result of transpose, instead of using the column letters as field names,...
Wicket's user avatar
  • 241
0 votes
Accepted

Hiding imported data in Google Sheets

Import all the data into a new 'Import' sheet, and refer to that sheet in your formulas. Then hide the 'Import' sheet. Note that a determined user can always discover all contents in a spreadsheet ...
doubleunary's user avatar
  • 12.6k
0 votes

Using multiple IF and OR statements with calculations

Two Approaches Reverse Order A more efficient approach that is based on your current IF function formula. Included a version for (a) the IFS function, and another with (b) multiple IF functions. ...
Blind Spots's user avatar
  • 2,984
0 votes

Return last value that isn't an error in each row

Another Approach Joel's solution works. This is simply an alternative. Basic Formula =INDEX(r,,XMATCH("*",IFNA(r),2,-1)) In the Basic Formula, replace r with a reference to a single row of ...
Blind Spots's user avatar
  • 2,984
2 votes
Accepted

Change cell color if data is changed from "initial value" in Google Sheets?

Try an onEdit(e) function simple trigger, like this: /** * Simple trigger that runs each time the user manually edits the spreadsheet. * * @param {Object} e The onEdit() event object. */ function ...
doubleunary's user avatar
  • 12.6k
1 vote

Automate range name creation in Google Sheets

Give this a whirl. It takes the DataRegion of your current selection (basically the contiguous used area you're in) and makes a Named Range for each column in the format sheetName_header. function ...
volfied's user avatar
  • 21
1 vote

How to filter combined search outputs with OR logic

To search each row in REFS!A3:AA for partial and case-insensitive matches of any of the text strings in AA1:AD1, use regexmatch(), like this: =ifna( filter( REFS!A3:AA, regexmatch( ...
doubleunary's user avatar
  • 12.6k
1 vote

How to shuffle a range but prevent refresh?

Assuming the range to shuffle is in A2:C, put this formula in cell H2: =let( refresh, H1, freeze_, lambda(_, _), randomNumbers_, lambda(r, c, randarray(r, c)), freeze_(randomNumbers_(999, ...
doubleunary's user avatar
  • 12.6k
0 votes

Cumulative Percentage Subtraction Google Sheets

This is an excellent candidate for the REDUCE function. Core Formula =REDUCE(score, SEQUENCE(absences), LAMBDA(s, a, s*(1-a*pct))) Exception Handling (Shortform) =IF(score>0, IFERROR( [...
Blind Spots's user avatar
  • 2,984
0 votes

How to filter combined search outputs with OR logic

Not Search SEARCH locates the position of a text string within another text string, not a value in a range. Example: =SEARCH("bob", "jimbob") =4 Solution To FILTER a range based ...
Blind Spots's user avatar
  • 2,984
0 votes

How can I replace blank cells with zeros in Google Spreadsheet?

Simpler Formula Since you are dealing with numbers and blanks, the accepted answer =IF(ISBLANK(A3),0,A3) is more complex than necessary. Simply adding zero to the cells will achieve the same result: =...
Blind Spots's user avatar
  • 2,984
0 votes

How can I replace blank cells with zeros in Google Spreadsheet?

Select an empty cell -> "filter by cell value" (it will only display empty cells in the column) -> fill in the value you need in the first cell -> drag it down all the way to the ...
Daria Dmitrochenko's user avatar
1 vote
Accepted

Google Sheets: Change text color of multiple cells if checkbox is checked?

Use this conditional formatting custom formula rule for the range B2:D: =$E2 See GimelG's explanation and Lance's explanation of how absolute and relative addresses work in a conditional formatting ...
doubleunary's user avatar
  • 12.6k
0 votes

Failure to autsort Google Sheets

The code in the question errors out with Exception: Cell reference out of range because range.offset(NUMBER_OF_HEADER_ROWS, 0) will always get a range that is partially outside the data range that ...
doubleunary's user avatar
  • 12.6k
0 votes

Add values in a row based on the date values in a sepperate row

Using SUMIF as suggested by @JohnSUN is a good approach and also slightly shorter (4 chars) than the alternative below. Your original approach would work if you tweak it to FILTER row 4 based on the ...
Blind Spots's user avatar
  • 2,984
0 votes

How do I change the number format for the horizontal axis labels in a histogram?

Strangely, in order for me to see this setting, I had to click the number on the axis. Only then did the setting to change the number format. From there, you can choose to use the format of the source ...
David Tan's user avatar
  • 949
1 vote

Creating rule for two cells equaling same value

Use conditional formatting =AND($B1<=0,$C1<=0)
Jorge Palacio's user avatar
0 votes

How can I group/count occurences of a column

You can use COUNTIF to count the occurrences. After that, copy the count column as values. Last, remove the duplicates
Jorge Palacio's user avatar
1 vote

Creating rule for two cells equaling same value

Use this conditional formatting custom formula rule for the range A1:D: =isnumber($C1) * (max($C1:$D1) <= 0)
doubleunary's user avatar
  • 12.6k
0 votes

How to use nested IF statements in Google Sheets?

You were close you just needed to nest your IFS functions in IF functions or an additional IFS. In both formulas below, 1 is a shorthand for TRUE for the last IFS condition. Nesting in Two IF ...
Blind Spots's user avatar
  • 2,984
0 votes

Filter function with multiple variables google sheets

Filters values in A2:A6 if either condition is true: =FILTER(A2:A6, (B2:B6<B10)+ ((C2:C6<B10)*(D2:D6="Y"))) In the FILTER function conditions: + is equivalent to OR * is ...
Blind Spots's user avatar
  • 2,984
1 vote

How do I ignore a few rows below the header (including header) when sorting data in sheets?

One easy workaround would be to move the notes above the label row so that it is no longer part of the filtered area. I do not think there is any turnkey way to keep notes where they are without ...
doubleunary's user avatar
  • 12.6k

Top 50 recent answers are included