Tag Info

New answers tagged

1

As long as you don't need to parse the contents of the subject line, you can do this with If This Then That. You'll need the Gmail and Google Drive channels. Specifically: Trigger Depending on how you're going to identify the incoming message, you'll use one of the following triggers: Any new email in inbox New email in inbox from New starred email in ...


1

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.


4

I would use the following formula to obtain an URL. Formula =IF(ISEMAIL(A2), REGEXEXTRACT(A2,"@(.+)"), IF(ISURL(A2), A2, IF(ISURL(REGEXEXTRACT(A2,"@(.+)")), REGEXEXTRACT(A2,"@(.+)"), "No valid entry" ) ) ) Copy / Paste ...


0

Copying with Ctrl+c and pasting with alt+e then s then f works for me. But as you are typing Cmd instead of Ctrl maybe you're on a Mac, aren't you? I don't know if this will work for you.


5

This formula will do the job: =regexextract(A1;"@(.*)") If no @ is found, the original value is shown. The =regexextract function (documentation) executes the regular expression parameter (@(.*)) against the value of cell A1, and returns the matching group.


1

This is an example of "List view" that features in the previous version of Sheets. It is no longer supported in the new version; to the best of my knowledge, I think it has been more or less replaced by the "Filter view" feature.


3

Here is what you can do: RightClick B2 → select Conditional formatting → select Custom Formula is (from the List) Enter the following formula in the textbox: =b2>=c2 Check the Background Color and select green color Click on Add another rule Select Custom Formula is (from the List) Enter the following formula in the textbox: =b2<c2 Check ...


1

I used this to solve my problem. =IF(ISTEXT(VLOOKUP($A6,responses!$D:$M,3, FALSE)),"Yes","No")


3

Sooo close (I think!). Please try: =D3=min($D3:$L3) =min(D3:L3) returns a value but CF needs a TRUE to trigger. So we check "the current cell" (D3) to see whether its value matches that of the minimum in the range D3:L3. If it does we trigger the formatting. I say "the current cell" because quite which that is depends on the Range: selection. Here it ...


1

If you download the old Doc as an XLSX, then upload and convert to Google doc version, it upgrades the doc. I haven't found anything it doesn't keep yet. It even kept my named ranges. [EDIT] I did have to "tickle" some functions by removing, then re-adding the = sign in front. Or double-clicking the cell, then just hitting enter. Maybe I just wasn't ...


0

Yet another option: Add this, from the menu option Format, under Number>More Formats>Custom number format... "+"#


2

In Dashboard D36 please enter: =sumif(log!C:C,A36,log!B:B) (should result in the 19 you mentioned) and then copy up to D4.


2

I think: deduplicate a table (all rows and columns) based on the values in Column A is not compatible with I only want unique values in column A, but I want all the other data to persist in the other columns, including cells with null values so choosing the latter: =UNIQUE(x:x) might serve where x represents whatever the column ...


0

Another way to do this using formulas is to add another row next to the one that you want to change into text. After you do this, inside the row, add =TEXT(G17,"+00000"). Then copy the formula down to the rows beneath it. Once you have done this, hide the row with the old numbers (Which should still be as a formula). This should make it so that the ...


1

Yes! Highlight the cells you want to format. Go to Format > Conditional Formatting. Once you're in the conditional formatting dialog, choose "Custom Formula Is" from the select box (it's the last option). In the text field type: =len(XY)>30 X will be the column you're in and Y will be the row you want it to start at. So if you want to check the entire ...


0

In Google Spreadsheets (or Google Sheets as they seem to be calling it now) there are two good options. Append a single quote to the beginning. For example with the phone number +1 415 655 1234 you would enter '+4156551234. This will be displayed in the Sheet at +4156551234. It will also export to a CSV as +4156551234 with out the ' which is extremely ...


1

I would put the progress-value into a column, and then use conditional formatting based on that to set the cell background. And then you can filter on the progress column directly. (I've never liked the idea of just colouring cells and then trying to extract the value of them: in principle, if you want to record some data then do it directly.)


1

You will need to resort to Google Apps Script for this: function onOpen() { var cell = "Sheet1!A1"; SpreadsheetApp.getActiveSpreadsheet().getRange(cell).setValue( Math.floor(Math.random() * 99)); } Ensure that you run the script manually first to go through the authorisation process.


0

With, in your summary sheet, the start date in A1, the end date in A2 and the selected category criterion in A3 then: ...


1

The following formula can be used to merge one column after another: ={A1:A5; M1:M5}


0

The absolute simplest approach is to just StoreNumber onto the form, and ask them to complete the form once for each store that they own. This is the easiest way to deal with the fact that they may own different numbers, eg one may have two, another four.


1

If your result depends on more than one cell, you can prevent anything from appearing until all necessary reference cells are populated: =IF(OR(A1="",B1="",C1=""),"",your_formula_here(A1,B1,C1))


5

Yes that's possible !! See the below-mentioned formula to do just that. Formula A2 = HYPERLINK("http://www.jacobjantuinstra.nl") =IMPORTXML( "https://plusone.google.com/u/0/_/+1/fastbutton?&count=true&url=" & A2, // URL "//div[@id='aggregateCount']" // xpath_query ) copy / paste ...


0

To build on the accepted answer, you can tweak that a bit to get checks and x's. Use two cells in your sheet, one to hold the formula =char(10003), and the other to hold =char(10005). Now follow the accepted answer exactly, but instead of "List of items" select "List from a range" and choose the two cells you just created. This will create a dropdown ...


-1

EDIT to include the essential information from the link, original answer here at bottom. The first two methods do not solve my problem, as cells referred to in formula continue to update the reference as extra records are added from the form. Method 3 answers my problem as specified in the question.... Method 1: Duplicate the sheet. Click on the little ...


0

I acknowledge that this does not answer "what is Google Spreadsheets’ new line character?". However, if not for OP for others wanting to get from a text file containing: hi,hello\r\nwelcome,to\r\npeaceful,world to : a process of the kind below may suit. The technique is basically to 'feed' Google Sheets with a replacement for \r\n that ...


0

As @pnuts already mentioned, a script is needed. But also this script will not give you exactly what you want. The onEdit() trigger is only triggered when something has changed and there's no onClick() event trigger available in Google Apps Script. Therefore I've created this small snippet for you to use. Code // global var ss = ...


2

A hack might be to make the font colour in the cell the same as its background colour, so nothing is visible 'normally' but on clicking the cell the contents would appear - in the formula bar. Otherwise I think it would require a script but I take it that is not what you want since your question is not tagged that way.


0

=average(C:C) should average the whole of ColumnC's numeric values, without difficulties over text or spaces in that column, while responsive to changes in ColumnC entries - with the proviso that the formula should not be in ColumnC.


0

In Format, Conditional formatting... the following should achieve what you request: where "Custom formula is" contains: =countif(C:C,C1)>1 and Range may be set to C:C - or adjusted to suit.


1

Please try a Custom formula is like so: =and(A1>today()-365,A1<today()-180) which may be applied not just to A1 but say to the whole of ColumnA if the Range: is A:A.


0

You can use a vlookup function =VLOOKUP(E138,Status!A:K,6,false) E138=the Control or reference number (i.e. name) that will be the same in this sheet and the sheet with your data Status!A:K=the tab that has your data and the columns that the data is in. the first column should be the one that has the reference numbers (i.e. name) 6=the column that has the ...


1

Since @jam 's answer there might be some improvement in Spreadsheet's features. If you'd like to preserve and use the numbers as figures (eg. automatic fill) you might apply the settings from the Format > Number > More Formats > Custom number format... menu, and set the pattern to 000, where the number of zeros represent the number of digits to ...


0

Maybe you want =countifs, see https://support.google.com/docs/answer/3256550.


1

Another alternative is to set up a Google Form, and have him enter the data there: that means he cannot even see the formulas.


0

I right-clicked on a cell (or several cells) in Google Docs Spreadsheets and there is an option "Name and Protect range" I assume you have seen this, and doesn't suit your needs? Can you clarify?


0

This is happening because you have put the formula inside the range (A1:c6) that you are trying to count the number of "free"'s in. Put the formula into a cell outside the range.


0

I need to auto fill sheet Report if sheet David contains "1" in column "i". I need to copy cells C14 - C34, J14 - J34 and L14 - L34 only if there is "1" in column "i" In the sheet where you want to put the data (I think that's sheet Report), put this formula into C14 = if('David'!$I$14=1, 'David'!C14, "") then copy and paste it into C15-C34, J14 - J34 ...


0

May not answer your question but might be of use: =sumif(Sheet1!B:B,"*Florida*",Sheet1!A:A)


1

Please try: =average(offset(A1,0,4,1,5)) (But don't add a column to the left of D that is to the left of A also!) Help with =offset, and other functions, is available via Help and Function list.


1

Please try: =vLOOKUP(R3,Calculations!$B$2:$C$7,2,0) For LOOKUP (as opposed to VLOOKUP) to work properly the data must be sorted. The anchors ($) are in case say the formula was to be copied down (eg to return a value from R4). Without them the array range would slide down automatically until by R9 the search would be of Calculations!B8:C13.


1

1) add a helper column which calcluates the month eg =date(year(A2),month(A2),1) 2) Use the Format > More formats > More date and time formats feature to show the helper column as month and year 3) Use the Data > Pivot table report feature to make a summary table Here's a worked example: ...


1

If the desired company name is in A1 I think you may have a use for something like: =query(List!A:U, "select * where A contains '"&$A1&"' and L is null and P contains 'Production Company' ")


2

I think you are getting the correct answer but .2857 is being rounded down to less than £1! (ie it is just the formatting).


1

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been ...


2

Please try putting the start and end dates (say in G1 and H1) and a formula such as: =sumifs(C:C,B:B,">="&G1,B:B,"<="&H1) Alternatively, since all your dates appear to be this year, you might extract the month number with something like: =month(B1) copied down to suit and then create a pivot table with that number for Rows.


2

I've just had a go at this, and here's a more flexible function. The previous example couldn't properly handle ranges of selected cells, this one can (but you could also add some better error checking for empty ranges!) Could easily be customized to have other ranges of brightness, hues, auto-calculation of max value, etc.: /** * Adds a custom menu to the ...


2

I'm afraid this is more answering "do you have any idea?" rather than giving a solution. The primary issue here is your locale, as mentioned in your comment on the question. Custom number formats are not agnostic to the locale-specific number formats. In the case of the UK locale, commas are regarded as thousands separators, and periods are regarded as ...


1

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg: function onEdit(e) { var editRange = e.range; var sheet = editRange.getSheet(); if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1") { var updateRange = sheet.getRange("A1"); var newValue ...


0

I think your best bet is to allow a pivot table to construct the options for you (though could be achieved with formulae in a matrix). Please try selecting your data and Data, Pivot table report..., with Student Name for Rows, Discipline for Columns and Summarise by: COUNTA Discipline.



Top 50 recent answers are included