Tag Info

New answers tagged

0

The QUERY function will do what I want. I had no idea that I could use database like functions in Sheets. =QUERY(IMPORTRANGE("insert_url","Range"),"Select Col1, Col2 Where Col4='Value'")


0

If you want to use forms there is a time picker element to select time. Otherwise, you don’t have a time picker. You got 2 options: You can create 2 columns includes hours and minutes and add a drop down, then generate time from those. You can add time correction to field. : between hour and minute concert valid time field.


0

Use a formula like this: =HYPERLINK("mailto:labnol@ctrlq.org", "Send Email")


0

With some more searching I found that someone wrote Google Sheets add-on that does just this. The add-on is Save as Doc. The output doesn't look exactly like the form, but it generates a Google Doc with page breaks after every row and you can specify the column titles as an level of heading. For simplicity it is pretty hard to beat.


1

I always use the ISNA() function. As a first step, I create the range with filter(): =FILTER( TRANSACTIONS! C4:C51; TRANSACTIONS! D4:D51="Other"); As a second step, I always use the: =IF(ISNA(range);0;thingsToDoWith(range)); So, in your case it will become: =IF(ISNA(FILTER( TRANSACTIONS! C4:C51; TRANSACTIONS! D4:D51="Other"));0;SUM(FILTER( ...


0

Significantly shorter formula than accepted answer: =SUM(FILTER(C1:F10, ISODD(COLUMN(C1:F10))))


0

Let's assume your spreadsheet has the id 1wJR0uH0v0KPex4uoma9Gy2IHCW5JnvQ-QWa6V1GtN5g (you can obtain the id of your spreadsheet by looking at its URL in your browser: it is between …/spreadsheet/d/ and /edit…). Let's assume your spreadsheet contains the sheets: overview test1, test2, test3 hidden (you'll add this last worksheet and hide it before you ...


0

Maybe: =averageif(A:A,"Male",B:B)


0

Try this: =HYPERLINK("http://www.google.com/search?q="&A13&L13&"&safe=active",A13) A13 uses whatever text is in the cell to search. It is also at the end of the formula to name the cell the formula is in. I've got “near my address” (well, “near _______”, whatever your address is) in cell L13, just remove &l13 to get rid of it. It ...


1

I'd use this formula in E3 and copy down: =IF(AND(E2<>"",OR(C3<>"",D3<>"")),E2-C3+D3,"") EXPLANATION: The IF statement contains 3 comma separated parameters: logical_expression value_if_true value_if_false In this case the logical expression is AND(E2<>"",OR(C3<>"",D3<>"")). AND takes multiple logical expressions as ...


0

I was able to successfully execute your constructed formula. Screenshot Most probably you have either white spaces before or after the text Total. You can use the following formula to check the length of the text for a column: =ARRAYFORMULA(LEN(C:C)) Removing those will (hopefully) yield 9. Good luck !!


1

I know this is an old topic, but for the number of unique articles, this might work: =ArrayFormula(counta(unique(filter(A2:A&B2:B, len(A2:A&B2:B))))) I also tried to work out some formulas for the other questions: Hope this helps ?


0

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then: Format, Conditional formatting..., Custom formula is: =match(A1,indirect("Roster"&"!D:D"),0)>0 select yellow (Range: should be A:A) and Save rules. For each colour you need a separate rule ...


1

I think mostly so that they can act like bookmarks, only better. I have a Google Docs icon on my desktop that will launch Chrome and go straight to my documents. Sure, I could drop a bookmark there just as easily, but by default it'll have the logo of my default web browser. Sure, I can change it, but it's a hassle. Also, since it's a "Chrome app", it can do ...


1

See if this helps ? function onEdit(e) { var s = e.source.getActiveSheet(), watchCols = [2, 5], offsetCol = [8, 10], ind = watchCols.indexOf(e.range.columnStart) if (s.getName() !== "Sheet1" || ind === -1) return; e.range.offset(0, offsetCol[ind]) .setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT", "hh:mm")) }


0

You need to do this. Formula new Google Sheet =REGEXEXTRACT(IMPORTRANGE("URL_IMPORT_SHEET", "A2"), "(\w+\.\w+)$") old Google Spreadsheet =REGEXEXTRACT(IMPORTRANGE("KEY_IMPORT_SHEET", "A2"), "(\w+\.\w+)$") For a range you need to do this. =ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("URL_OR_KEY", "A2:A14"), "(\w+\.\w+)$")) References URL or KEY: ...


2

This is what you need to do. Formula =QUERY(A1:C2, "SELECT * OFFSET 1", 0) Explained The SELECT statement contains an extra OFFSET argument. It tells the query to skip one row. The third parameter of the QUERY formula controls the headers. Setting it to zero will ignore the headers.


3

What you're looking for is conditional number formatting. You can do this in the Google doc by highlighting the column you wish to format and clicking: Format → Number → More Formats → Custom number format... Thereafter, specify this as your formula: [<10]_(###0.00_);_(#0_) What it means is, if the cell value is less than 10 then format the cell in ...


2

This is your key: 11jp7-1POyw_cunE1imQ80Bqxj8U93a4hvHmKpWLY5K8 As @Punchlinern mentioned in his comments, with the new Google Sheets you can use the entire URL in combination with the IMPORTRANGE() formula. Using the above-mentioned key, the old style, works as well. Reference: IMPORTRANGE


1

You would need a google apps script for that. Something like this should work: function onOpen() { SpreadsheetApp.getUi() .createMenu('Custom Menu') .addItem('Select Range', 'selectRange') .addToUi(); } function selectRange() { var ui = SpreadsheetApp.getUi(); var sheet = SpreadsheetApp.getActiveSheet(); var response = ui.prompt('Enter the ...


1

Assuming you meant 'selecting from a certain cell from all sheets' (in one spreadsheet), custom function written in GAS that does that could look like this: function avgCellAllSheets(cell) { var arr = []; SpreadsheetApp.getActive() .getSheets() .forEach(function (s) { arr.push(s.getRange(cell) .getValue()); }); return ...


0

Here is a Custom Function code snippet to get a spreadsheet name by it's Identifier. function GetSpreadsheetNameById(id) { var ss = SpreadsheetApp.openById(id); return ss.getName(); } In a cell, pass a sheet Identifier (look in the spreadsheet URL for the identfier). =GetSpreadsheetNameById('49nvkwOE-203nXDLGGJtg0htFKK020dsd939AAPZU')


0

Key for the sort is the notation of the digits in the text (String #). These are sorted alphanumerically, meaning that 9 is higher than 25 when sorting descendingly. This can be resolved by squeezing in a zero to all digits ranging from 1 to 9. See formula I constructed. Formula =SORT( // range UNIQUE( // range ...


1

If your 0:01:00 is a date/time index (numeric rather than a string) the underlying number is an integer for days and a decimal for time. as there are 24*60*60 seconds in a day the number of seconds represented by 0:01:00 is effectively the number of seconds between midnight and one minute past midnight, which for A1 can be calculated as: =86400*A1


0

I suggest a helper column (ColumnC) populated in C2 with: =value(substitute(mid(A2,find(" ",A2)+1,2),"/","")) copied down to suit, then sorted by ColumnC not including header. MID take the first and second characters after the first space (located with FIND) and since one of those might be /, SUBSTITUTE replaces any / with nothing. VALUE then ...


0

I suspect your requirement can be better fulfilled with Filter views, a feature of the new Google Spreadsheets: When creating a filter view, click on a column header, and define the filter: When done, click the close the filter editor, and your filter view will be available from Data → Tools → Filter views: And, of course, the filter view ...


2

Please try: =Query(Data!A6:L1397,"Select A,B,C,D,E,F,G,H,I,J,K,L where A>=date '"&TEXT(A1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B1,"yyyy-mm-dd")&"'") Note Column references for the source data set and that the likes of date '"&TEXT(A1,"yyyy-mm-dd")&"' are to accommodate the possible use of =today(). May be worth mention ...


1

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method. You are also referencing a variable sheet, which you haven't defined anywhere. Currently, your code is more suitable for going through a single sheet, you need to iterate through the ...


0

Here's a custom formula that does the same. Code /** * Create a cross join of two ranges * * @param {range} students The range with student names * @param {range} days The range of dates * @param {range} header The range with the headers * @return A cross join of the two ranges * @customfunction */ function crossJoin(students, days, header) { ...


2

assuming you have the names in A2:A5 and the dates in B2:B5, try this formula: =ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))}) Example sheet with formula in D2 explanation: This formula consists of two parts, forming a literal array ...


0

Disclaimer: I work for Ragic. I agree that manually creating tables in Google Sheets would be very time consuming for you even though it might be a good idea for simpler tables. At Ragic you can simply build these forms and reports, you can start by following the normal process of developing a sheet. If you need help, just ask our friendly support staff, ...


0

You could try MATCH: A1 = [Value you are trying to find] B1:B10 = [Range you are searching in] =IF(ISERROR(MATCH(A1;B1:B10;0));"Not found";"Found") You can replace the A1 with formulas that return a value to search for. The 0 ensures actual search, not presupposing an ordered search range. Ref: Documentation for MATCH


0

I figured out the issue. From Google: Sample Usage INDEX(A1:C20, 5, 1) Syntax INDEX(reference, [row], [column]) reference - The array of cells to be offset into. row - [OPTIONAL - 1 by default] - The number of offset rows. column - [OPTIONAL - 0 by default] - The number of offset columns. I needed to offset by column, not ...


0

Turns out I was using the SUMIF formula wrong. This works: =SUMIF(A1:A,"",B2:B)


0

If you are ok with using closed ranges, try: =sum(ArrayFormula(if(B2:B100="", offset(B2:B100,1,0),0)))


0

Provided that last column is populated, then something like: =HLOOKUP(1E+100,B1:Z1,1) assuming a row starts in ColumnB and ends in ColumnZ.


0

Ideal case for Conditional Formatting (Format - Conditional formatting....), having selected A1:D4: Choose Custom formula is (only available in new Google Sheets), Enter there: =A1=max($A1:$D1) Select your formatting, Save rules.


1

The IMPORTRANGE function does not seem to cache its results, so it will refresh whenever the source spreadsheet changes. But we can circumvent this by writing our own function: function customImportRange(spreadsheetUrl, rangeStr, timestamp) { var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl); var range = sheet.getRange(rangeStr); return ...


0

Please try, in B2 of Expense_Overview: =sumif(Expense_Detail!D:D,A2,Expense_Detail!C:C) copied down to suit, or: =ArrayFormula(sumif(Expense_Detail!D:D,A2:A,Expense_Detail!C:C)) You have a treat in store whenever you get around to pivot tables. They are very powerful but incredibly easy to use for all that: Select A1:D7 in Expense_Detail, Data, ...


1

This is a derivative of the other answers but it might be useful for future users. function onEdit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); var bugCount = sheet.getRange("M2").getValue(); bugCount++; if (sheet.getRange(row, 1).getValue() == "") { ...


1

Try: =mod(C2-B2,1) and drag down as far as needed. Make sure the format the cells as duration. For the 'total time' a simple sum should suffice.


0

Select all the cells in column B --> Conditional formatting --> Custom Formula= B1:B5>$A1 Hope it helps.


0

You need to send HTML email to do that. In Google Apps Script you do that by specifying a htmlBody element to the MailApp.sendMessage method: MailApp.sendEmail({ to: "recipient@example.com", subject: "Your link", htmlBody: "Here is your example link: " + "<a ...


1

You can set up an onEdit trigger that will log the edit date and other details in another sheet inside the same spreadsheet. function onEdit(e){ var range = e.range; range.setNote('Last modified: ' + new Date()); }


1

You might try: =CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))) strung together with & three more times.


0

Perhaps you can format it the way you want by using this in the first place. Code var start = row[5]; // "Fri Feb 20 2015 00:00:00 GMT+0200 (SAST)" var newStart = Utilities.formatDate(new Date(start), "SAST", "EEE MMM d yyyy"); Logger.log(newStart); Screenshot


1

Have you tried the .toDateString() method ? See what this gives you: var start = row[5]; var dateStr = start.toDateString(); Logger.log(dateStr)


0

You're basically wanting to format a date. I would act on the JavaScript Date object, something like: var startDate = new Date(row[5]); var weekDay; switch (startDate.getDay()) { case 0: weekDay = "Sun"; break; case 1: weekDay = "Mon"; break; case 2: weekDay = "Tue"; break; case 3: weekDay = "Wed"; break; case 4: weekDay = "Thu"; break; case 5: ...


2

Maybe this is what you need ? =IF(A4=2;1;IF(A4=4;7;A4)) or, depending on your locale: =IF(A4=2, 1, IF(A4=4, 7, A4))


0

This might be correct: =INDIRECT(datasourcepoint!$A$1&"!A"&ROW(B2)) I am actually fetching the sheet name from another sheet (it just contains the name of the source data sheet to use). The trick was to add &ROW(B2) instead of 2" towards the end.



Top 50 recent answers are included