Tag Info

Hot answers tagged

4

Unfortunately, Data Validation is applied only to user-entered data. If there is no data in a cell, validation rules do not run on it. See Empty data cell validation on Stack Overflow. Some surrogates, which can be used in combination: Conditional formatting. Make the background red if the cell is empty. A message in another cell, such as ...


3

Google has added a new number format called Duration. Set your start and end fields to Format -> Number -> Time and your calculation field to Format -> Number -> Duration Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.


3

In addition to previous contributions, this should also work: =sumproduct({$C$3:$C$9; $F$3:$F$9}=A12, {$B$3:$B$9; $E$3:$E$9}) and drag down. The first literal array {} makes one column out of the C-range and the F-range. The second one does the same with the B-range and the E-range. When the 'first column' equals the contents of cell A12, the 'second' ...


3

Coercion in js is amazing and in this case you could simply use like this sheet.getRange("B2").setValue(A + "") Simply plus the empty string to your value. And for something in the similar but reverse way you can use +myString Just using an unary + operator this will coerce the string to a number. Find more about js coercion with a super sensei ...


3

The problem was due to Google Forms adding a new row every time. If you are having the same problem here's what you need to do to fix it: Where you previously had your formula change it to: =arrayformula('YOUR SHEET NAME'!A2:A) Change the sheet name, and enter the cell you want to target, as-well as the column. Make sure you delete all other formulas ...


3

In Google Spreadsheets, you can use the UNIQUE() formula to do that. Formula =UNIQUE(A1:A10) Explained For the range as seen in the screenshot, there are 10 entries. The UNIQUE() formula accepts a range and filters out the duplicates and returns that range, see screenshot, leaving only 7 unique entries. Screenshot Reference Google Spreadsheets ...


3

The characters you enter in the filter box are matched to the beginning of any word in the cell content. So, @ will match "a @ b" or "a @b", but not "a@b". To filter based on a pattern that is not a beginning of a word, you can use a workaround with REGEXMATCH, e.g., entering in another column: =IF(REGEXMATCH(A2,"@"),"email","not email") and then ...


2

This formula will work to keep blank cells from returning "0" to another cell. =IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero") Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage ...


2

This (presumable) oversight has since been corrected: here is the current screenshot of publishing a new Google Sheets document.


2

Maybe give this a try: function onOpen() { SpreadsheetApp.getUi().createMenu('Custom Menu') .addItem('Make Negative', 'makeNegative') .addToUi(); } function makeNegative() { var res = [], range = SpreadsheetApp.getActiveSheet().getRange('D2:E'), val = range.getValues() .forEach(function (r, i) { r[0] = r[1] === 'debit' ...


2

Assuming you have a new line character in every cell and you want to extract everything before the new line, try: =ArrayFormula(iferror(regexextract(A2:A, "^(.+)\n"))) (where A is the column with the addresses and your data starts in row 2 --> change to suit). After that, you can copy the output and use 'paste special' (values only) to overwrite to ...


2

Try: =query({A:A, A:A}, "Select Col1, count(Col2) where Col1 <>''group by Col1") or if you don't want the header 'count': =query({A:A, A:A}, "Select Col1, count(Col2) where Col1 <>''group by Col1 label count(Col2)''") An alternative way would be: =ArrayFormula({unique(A1:A6),countif(A1:A6, unique(A1:A6))})


2

Yes - there is a small dropdown menu in the top right corner of the comments menu: Click the Delete menu item, and the comment thread is gone.


2

This little code makes it possible to create shortened URL's, with the use of Google Apps Script and the URL Shortener API. Prerequisites Under resources in the script editor (Tools > Script editor) select the Advanced Google Services and activate the UrlShortener: Then go to the Google Developers Console and select the URL for the API: Enable the API ...


2

Use a Data Validation with a custom formula. If the cell to limit the value length is A1 and the length limit is 5, then the custom formula is =LEN(A1)<=5 References Data Validation - Docs Editors Help


2

The following little script limits the cell input to 5 positions and adds the surplus as a note to the cell. Code function onEdit(e) { if(e.value.length > 5) { var c = e.source.getActiveCell(), v = e.value; c.setValue(v.substring(0,5) + "..."); c.setNote("Remaining text was: \n....." + v.substring(5)); } } Explained On each and every ...


2

I think this requires a script, such as this one: function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getDataRange(); var values = range.getValues(); for (var i = values.length; i>=1; i--) { if (values[i-1].join("")) { sheet.insertRowsAfter(i, 1); } } ...


2

This is a job for regexreplace. Suppose A1 contains the text Membership and Volunteer Crew - do you like getting to know lots of new people? | Sauna Crew - Spend time sweating and supervising our sauna activity. | Crafters Crew - find and invite and present regional Crafts People at R&S and help them present workshops to our audience. Put the ...


2

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


1

Try =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where Col18='"&E3&"' and Col19='Installation Booked' ")


1

This script will do the trick: /** * Replaces some spaces with newlines in str, specifically, ' a.' becomes '\na.' for any letter 'a'. * See http://webapps.stackexchange.com/questions/76035/how-to-add-line-breaks-in-a-cell-before-specific-keywords-in-google-sheets */ function InsertLineBreaksInString(str) { return typeof(str) === 'string' ? ...


1

This is more a workaround than a real solution, but add this to the scripteditor and change to sheet name.. function onEdit(e) { if (e.source.getActiveSheet() .getName() !== 'Sheet2' || e.range.columnStart !== 7) return; e.range.setFormula("=rept(char(9734)," + e.value + ")") } Since I do not know how to work with a star symbol (char) in GAS, the ...


1

Try: =ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9)))) or: =ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9)))) or also: =ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))


1

Check out this formula I entered in G2 of the sheet 'SHEETJP' in the spreadsheet you shared: =ArrayFormula(vlookup(query({row(A2:A),sort(A2:E)},"select max(Col1) where Col2 is not null group by Col2 label max(Col1)''",0),{row(A2:A),sort(A2:E)},{2,3,4,5,6},0))


1

.. and for the first question, assuming your data start in row 2, in C2 try: =ArrayFormula(if(len(A2:A)*len(B2:B), B2:B-A2:A,)) This formula should automaticcaly output the difference between B and A when data is entered. See if that works ?


1

Im going to go with the assumption that what's happening here is not your connection. This is actually caused by Google servers not allocating enough resource to your requests. The reason the calls are so long is because the server is processing them so it waits for the data to return in whole before it stops. If it doesn't get enough data in x amount of ...


1

This solution isn't exactly what you asked for, but certainly a lot easier to maintain. If you position the data vertically (see screenshot data), then the following formula will suffice and still work when adding more weeks without having to make adjustments. Formula =QUERY( B3:C, "SELECT C, SUM(B) GROUP BY C OFFSET 1 LABEL C ...


1

This can be solved with the SUMIF function (documentation). In cell B12, enter: =SUMIF(C$3:C$9, A12, B$3:B$9) + SUMIF(F$3:F$9, A12, E$3:E$9) SUMIF takes 3 arguments; the first being a range to be matched against a search value (second argument), and the third being the values to be summed. So SUMIF(C3:C9, A12, B3:B9) looks at the C3:C9, for each cell ...


1

You'll want to make use of Named Ranges. In this case, the range of cells is one. Select your cell Toolbar → Data → Named ranges... (or right-click on cell and select Define named range) On the right you will notice a new Named ranges sidebar appear Edit out "NamedRange1" to whatever you prefer to name the cell Double-check the cell reference below your ...


1

You can use the SUMIF formula to perform this type of calculation. Formula =SUMIF(Sheet2!A:A, A1, Sheet2!B:B) + SUMIF(Sheet3!A:A, A1, Sheet3!B:B) Screenshot Sheet2 & Sheet3 result Explained The first parameter of the SUMIF formula takes on the criterion range (column A in your example). The second parameter is the criterion itself. I needs to ...



Only top voted, non community-wiki answers of a minimum length are eligible