New answers tagged

0

I was able to solve the issue by having a cell $B$1 keep track of how many columns each set of data has, then multiplying the current row number by the number of columns in the set. So since there were 10 rows per set, $B$1 = 10. Every row this is pasted down gives row 3, column 10,20,30,40,etc. INDIRECT($A$1&" Data!R3C"& ((ROW())*$B$1) &"",...


0

There is no "exportrange" in Google Sheets. Use importrange where the first parameter is the URL of the spreadsheet, from which the data is to be taken, and the second points to a cell. It will look like this, where ... depends on the source spreadsheet: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/.../edit#gid=0", "Sheet1!A1") The first time ...


0

In my experience, the cells remember any format they were given. Once a column is formatted M/d/yyyy it should stay that way. There is no setting (other than the country setting) that controls the default date format. But entering the following script in the Script Editor (under Tools) will automatically format any dates you enter or edit from now on as M/...


2

I describe two approaches. Rich text One way is to create a column of hyperlinks in Google Docs (or any other editor that supports hyperlinks) and then copy-paste it into a spreadsheet. The result will be hyperlinks which are rich text, not formulas. Caveat: at present, there is no way to extract the URL from such hyperlinks with spreadsheet formulas or ...


0

You should mark column AG in some way so it can be identified as the column that indirect needs. For example, by placing "Data" in the first row of that column. Then you can use match to find the number of that column, which becomes a part of cell reference in R1C1 notation. =indirect($A$1 & " Data!R3C" & match("Data", 1:1, 0), false) This ...


0

This is the job for query function: =query(A:B, "select todate(A), max(B), min(B) where A is not null group by todate(A) order by todate(A) asc label todate(A) 'Date', max(B) 'High', min(B) 'Low'", 1) Here is the query string with line breaks for readability: it's mostly self-descriptive. select todate(A), max(B), min(B) where A is not null group by ...


1

Here's my simple way of going to the last row in a Google Sheet: Insert "xxx" or any other unique string of characters in the last rown of your spreadsheet (leave some empty rows above where you put the xxx). Type "CTRL F" to start up the Find dialogue box. Type "xxx" and hit Enter. Bingo you're in the last row of the spreadsheet. As you fill in the last ...


2

The docs say the list has to be "sorted" Yes, this is the problem. lookup expects the range to be searched to contain a list that is sorted alphabetically (if it contains text strings) or by value (if it contains numbers). If this expectation fails, it will not tell you anything but will likely return a wrong result. So you have two options. (bad) ...


0

Use conditional formatting with custom formula =len(join("", B1:1)) The join concatenates all the values in that row, starting with B1. The formatting applies if the length of the concatenated string is not zero.


2

May be an old question but its still being looked up - so here is the workaround: Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data ...


0

Here is an Apps Script that searches all of current user's spreadsheets. If you have a lot of spreadsheets, it might time out of hit some execution quota, in which case it may be better to restrict it to a folder: replace DriveApp.getFilesByType with DriveApp.getFolderById('folder id here').getFilesByType The value to search is entered on var value = ...


1

One can get the last date of a list by sorting it in descending order and applying array_constrain. For example (not using your data structure) =array_constrain(sort(filter(B:E, C:C < today()), 2, false), 1, 4) means: among the rows where the date in column C is prior to today, select one with the latest date in C. The sort happens by the 2nd column ...


1

It can't be done using the Google Drive search feature but it can be scripted using the Google Sheets API. First you need to retrieve the list of spreadsheets that you have access to. Next, you loop over those worksheet IDs and perform a search within the rows, looking for the "$1000" value or anything else you want to find. If there is a matching row then ...


1

You can do you your match with the simple VLOOKUP formula if data is within different sheets on the same spreadsheet, but the example from codingbadger is missing an argument (has to have "FALSE" for the formula to only deliver exact matches, it won't work if different or missing). Sample Usage: VLOOKUP(10003, A2:B26, 2, FALSE) Syntax: VLOOKUP(...


0

The following formula does this: =if(date(year(today()), 4, 6) < today(), date(year(today()), 4, 6), date(year(today())-1, 4, 6)) It constructs date(year(today()), 4, 6), which is the 6th of April of the current calendar year. If this date is prior to today, it is the output. Otherwise, the year is reduced by 1.


2

Unfortunately, no. The only ingredients for a normal Facebook status update are "UpdatedAt", "Message", and "From". A link status update does offer a "Link" ingredient, but that's the URL of the link you're posting, not the link to your status message.


0

In order to limit the range of a script, get the row and column of the active cell, and check that it falls within some bounds: function onEdit(e) { var row = e.range.getRow(); var col = e.range.getColumn(); if (col >= 7 && col <= 9 && row >= 25 && row <= 47) { if (e.value == 1) { e.range.setFormula('=CHAR(...


0

ss.getActiveRange() selects the currently selected or edited range. From reading your script I see you meant the entire range of data, which would be ss.getDataRange(). Other than that, the logic is okay. Other suggestions: Don't use methods when unnecessary, you get time and tablet that are never used. Consider getting all relevant values in one ...


0

Short answer Assuming that you want to compare a date in the cell A1 with the current date, use the following formula: =A1>TODAY() The above will return TRUE if the value of the cell A1 is the greater than the current date. Explanation In Google Sheets the comparison functions and operators could be used with dates if they are properly formatted. ...


2

Use arrayformula to perform operations on arrays, for example =sparkline(arrayformula(B2:E2 - A2:D2)) plots the sparkline of differences B2-A2,..., E2-D2.


1

Google Sheets cells doesn't include an age property, so you should decide how do you will record the cell last edition date, once you have decided maybe you could use the built-in conditional formatting tool. In either case you could use Google Apps Script to change the cell formatting.


0

If the string you're starting with, say 3 1 0, is in cell A1, the following function generates a random string such as 1 4 1. =left(join(" ", {randbetween(1,4); randbetween(1,4); randbetween(1,4); randbetween(1,4)}), len(A1)) The idea is to generate an array of four random numbers, join it into a space-separated string, and then cut the part of the string ...


1

I am able to achieve this in two ways: Turn off word-wrap on every cell in the row (as everyone has already mentioned) Replace all line breaks inside the text with 1000 spaces (The 1000 spaces will appear as a line break) Replace all tabs inside the text with 4 spaces (The 4 spaces will appear as a tab) I have written a script to be run from Tools->...


0

Nested IF statements The formula =if(A1="action a", 5, if(A1="action b", 7)) returns the value according to the content of cell A1. Lookup table Somewhere on the sheet, say in columns G:H (or on another sheet) make a table: action a 5 action b 7 Then the formula =vlookup(A1, G:H, 2, false) will return the value from column H that ...


1

Indeed, when forming regular expressions using join("|", C2:C), it is imperative to avoid blank cells in the range. To do this, filter the range: join("|", filter(C2:C, len(C2:C))) The reason for using len here is that it returns 0, interpreted as False, for empty cells, and nonzero numbers, interpreted as True, for other cells.


0

Apparently, you want to know how to use if statements with multiple possible outcomes. This is done by nesting them: =if(condition 1, outcome 1, if(condition 2, outcome 2, if(...))) In your case: =if(G40 = "Bin Run", I18, if(G40 = "Single Clean", T25, if(G40 = "Single Clean Bagged", Z42)))


1

Comments Comments cannot be copied. They are not considered part of the contents of a spreadsheet, they are more like chat messages, the record of a conversation that took place about that spreadsheet. If there was no such conversation about a copy of that spreadsheet, comments won't be there. Alternative: use notes instead of comments. They get copied ...


4

how can I input a value that would evaluate to FALSE that is not a number? Use ISNUMBER. Syntax ISNUMBER(value) value - The value to be verified as a number. *ISNUMBER returns TRUE if this is a number or a reference to a cell containing a numeric value and FALSE otherwise.


0

I have expanded the functionality of the script from the linked question accordingly. First it checks whether the sheet with given name already exists. If it doesn't, the sheet is created and the headers of the form responses sheet are copied there. Otherwise, the existing sheet it used. Either way, the newly submitted data is then copied to the sheet. ...


0

You wrote '"Brentwood" in column C', but in the actual form, the location (Brentwood) is in column B. Anyway, since location is not returned by the present query, it is too late to try to narrow the results down after running it. But you can filter before querying: replace each range such as 'Form Responses 1'!W2:AB by filter('Form Responses 1'!W2:...


1

This can be done with a script. Enter the following in Script Editor, found under Tools. Change the 'Name' to whatever is the name of the form field that should be used. function formSheet(e) { var sheetName = e.namedValues['Name'][0]; var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName); sheet.getRange(1, 1, 1, e.values.length)....


1

I would use filter instead of query. The function =counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14)) counts the number of nonempty entries in the row with the date that falls after today()-14 but not later than today(). The logic is simple: filter the rows, returning the content of their columns S through AF, then apply counta. To get the ...


0

Adding additional cell (which can be hidden after) to copy changing cell's data (... .setValue(time)) and manipulate the data in other sheets can be a quick solution in some variants.


-1

Following hints in the answer by Aurielle, I've been able to produce a shorter formula: =(A2:A)=text(max(arrayformula(value(substitute(A2:A,"T","")))), "T#") It assumes that the first row is occupied by table caption. Break down: substitute(text,"T","") removes letter T from a cell text text. value() converts the text result of substitute to a number. ...


0

An alternative to Aurielle Perlmann's formula =Value(Right(A1,Len(A1)-1)) = MAX( FILTER( Value(Right(A:A,Len(A:A)-1)), ISNUMBER(Value(Right(A:A,Len(A:A)-1)) ) ) Right() is used to get the number from the cells as all values have a prefix of length 1. FILTER is used to avoid to have to update the formulas when more values be added.


3

This formula looks really complicated but really its just a long nested arrayformula: =IF(A:A=INDEX(A:A,MATCH(TEXT(MAX(ARRAYFORMULA(if(istext(A:A),VALUE(SUBSTITUTE(A:A,LEFT(A:A,1),"")),))),"#"),ARRAYFORMULA(SUBSTITUTE(A:A,LEFT(A:A,1),"")),0)),TRUE,FALSE) You want to use conditional formatting with a custom function : You can see the image below but to ...


0

If I read your formula correctly, you wanted the Max Value of all the values in column A but since A has T1, T2, T3 ... T9999 - you need to grab the numeric value of column A, and get the max of it. It does looks like the OP is trying to re-apply existing working formula and extending it, but unfortunately due to the formula limitation (not even MS Excel ...


0

The referred site use JavaScript to load several tables from other files after the user click on the "All Reports" link. The solution is to find the source file of the desired table and use its URL instead of the one considered in first place.


0

Answering my own question: =-$A$1 in a less than built-in condition The = sign in front of the expression makes it an expression. Without it Google Sheets does weird stuff.


1

Here you go to have the two different colors you need to have your conditional formats in order, so the first one being the red: you can use format cells if less than or equal to and put -1000 as your value - see image below and then for the rest: What I did was use indirect and address to dynamically refer any given cell to itself, and then point to a ...


-1

I created a sheet that uses filters that allow all kinds of exports. Works great - hope it helps others Shared here: https://docs.google.com/spreadsheets/d/14nOGCyu2nOLDQ_cJNQfVsDiBxy-Te3W8eh9du3C4sVQ/copy


0

Try using this: =VALUE(RIGHT(A4,LEN(A4)-1)) And then add your formula as needed. To understand the formula: RIGHT returns text from the end of a string, as marked as the "length". I used LEN (length) to return the length of a cell, and minus 1. Example: T1 will have a length of 2. RIGHT of T1,1 will return 1. Example: T245, will have a LEN of 4. So ...


1

In case if you need to add a new row on top (first row) and copy formula from the first top row then you'll need to copy formulas across using getFormulas() and setFormulas() functions. You can change the value of firstRow to 2 if your spreadsheet has headers for example. function addFirstRow() { var firstRow = 1; var sh = ss.getActiveSheet(); ...


1

You can also use indirect and address to dynamically refer to each of the columns in the evaluation. =countif(indirect(SUBSTITUTE(ADDRESS(1,column(),4), "1", "")&":"&SUBSTITUTE(ADDRESS(1,column(),4), "1", "")),A1)=1 indirect is used to point to a cell reference that is in the string format - and you are correct in your comment, that basically ...


1

Well, after a bit of fiddling, I found a solution that currently works: Specify the cell range as desired: for me whole columns, starting with column A and ending with column BB, so it is A1:BB Then the custom formula should be: =countif(A$1:BB,"="&A1)=1 It seems like the Data Validation operation the places this "validation formula" (which must ...


0

To keep track of running maximum of the content of some cell, one needs a script that runs every time the cell changes value. Such a script could be triggered by the same button you are using to force recalculation, although a delay (Utilities.sleep(1000), time in milliseconds) may be necessary to make sure the recalculation runs before this script. Or, if ...


1

I was working on this a few months ago and came up with a very simple kludge: create a new sheet with the name of each cell as its contents: Cell A1 could look like: = arrayformula(cell("address",a1:z500)) Name the sheet "Ref". Then when you need a reference to a cell as a string instead of the contents, you use: = some_new_function('Ref'!C45) Of ...


0

In a cell of your choosing enter the formula: =max(A1:A7) Where A1:A7 are the cells of interest. A1:A7 interprets as cells A1 through A7, in a column. A1, A2, C1, C2 interprets each cell is looked at indiviually. A1:A7, B1, C1:C3 or any combination. =max(A1:A7, B1, C2, D1:D20)


1

If somebody likes monster-formulas, here's single formula solution: =TRANSPOSE( QUERY( UNIQUE( ArrayFormula( { TRANSPOSE(SPLIT(CONCATENATE("\"&FILTER(Data!C2:F,Data!C2:C<>"")),"\")), TRANSPOSE(SPLIT(CONCATENATE("\"&row(offset(Data!C2,,,counta(Data!C2:C)))*COLUMN(Data!C2:F2)^0),"\")) } ) ),...


2

The following formula counts the rows of Data sheet which contain the string given in cell A1 of current sheet: =countunique(arrayformula(if(Data!$C2:$F = A1, row(Data!$C2:$F), ))) Explanation: the arrayformula replaces each matching string with the row number, and leaves other cells blank. Then countunique counts the number of distinct entries within ...



Top 50 recent answers are included