Tag Info

New answers tagged

0

Turns out I was in some sort of preview view, and had to click "pop out" to actually open the document. All in all an impressively appalling bit of UI design from Google.


0

You are looking for a label. This is the format: LABEL A 'MyLabel' in this case you just need to eliminate the actual SUM label so you have to set it empty like: LABEL SUM (A) '' Just put it like this: =QUERY('Scheda Intervento'!C2:K14;"select SUM (K) where (F>= date '2013-01-01' and F <= date '2013-01-31' and C = '" & A2 &"') LABEL SUM ...


0

It sounds like you need to use the vlookup function. In a helper-column say vlookup(cell-with-the-name, sheet-and-range-reference, column-in-the-range-with-email-in-it, 0)


-1

Let me know if I'm wrong. I assume that both the work you have mentioned is related. I mean you are searching the email address of the person who is evaluated through a from in another spreadsheet and add to mail merge. If this is the scenario, you can design a form in excel and write a VBA script to fetch the email of the evaluated person from another ...


1

Fill A1 red (normal fill) and apply a Conditional formatting rule of Custom formula is : =counta(B1:B7)=7 with green Background Colour and Range: A1


0

Does the individual who sent you the document have you listed as "having permission"? Google docs have various separate permission settings for who can view, edit, etc. The individual may not have specifically given you permission to either view or edit the document. For example, one can give permission to view but not edit. Another setting may allow ...


0

I was getting the same. The problem is that there is no row 0... You should use i+1: var cell = sheet.getRange(12,i+1);


1

The Conditional Formatting rules can accomplish that. Result: Rules:


0

The following script will add a 0 to every empty cell in the range. Code function onOpen() { var app = SpreadsheetApp.getUi(); app.createMenu("Zeros") .addItem("Sheet", "zeroSheet") .addItem("Data range", "zeroDatarange") .addItem("Selection", "zeroSelection") .addToUi(); } function zeroSheet() { var sheet = ...


0

It sounds to me like you have a fundamental design flaw if each month is a new sheet, and there is nothing on the sheet to show the current month. I would suggest putting all the data onto one sheet, and then adding per-month extracts onto other sheets using the various summarisation and extraction tools (pivot tables etc).


2

Please try: =IMPORTXML("https://www.youtube.com/watch?v=j6RsJmelphk","//div[@class='watch-view-count']") which should return something like 97,000 (views for a Colin Farrell - Road to Hell video).


0

For a limited number of columns you might filter to select Blanks, then insert and copy down 0.


2

If your Duration is a string then something like: =(left(A2,2)+mid(A2,4,2)/60+right(A2,2)/3600)*B2 otherwise: =A2*B2*24 with the result formatted as Number.


2

I assume you only want to do this in a certain part of the spreadsheet, and that it's a one-off problem which you need to fix, not something you need to repeat. For this, the simplest approach is to find another area the same shape and size (perhaps in another worksheet), and not currently used. In each cell in that area, set the forumula to something like ...


0

This does the trick: =IF(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)=INDIRECT("Default!R"&ROW()&"C"&COLUMN(),FALSE),TRUE,FALSE) It's not exactly concise, but it works.


0

Can you just put the results of =rand() into a helper column, and then pass the contents of that column into your function? That may resolve the hanging behaviour you are seeing.


1

That’s because the respective help page is referring to Google Docs (the text editor), not Google Sheets. And you are trying to insert it in a Google Sheets document.


2

It seems to be a known Firefox bug: https://bugzilla.mozilla.org/show_bug.cgi?id=948510 (Alas, it was opened more than year ago…)


0

I think you want something like: =query(Submissions!B:C,"select B, C where C = 1") where the number would need to be changed as the formula is applied in different sheets.


1

Chrome (Windows, Linux): ALT+I, then R IE & Firefox (Windows, Linux): ALT+SHIFT+I, then R Mac: CTRL+OPTION+I, then R This will add a row above. Instead of R you can use... W to add row below C to add column to the left G to add column to the right


0

Insert a new column just to the left (or right) of the column you want to change. In the top cell of the new column (Say it's cell G23 and your data is in F23:F32) put i: =F32 & " With only G23 selected, pull down on the little square on the lower right to fill the formula down to G32 - This will propogate the formula down, with the row # incrementing. ...


0

The following Add-on is available in the Add-on store from within your Google Spreadsheet. Add-on Link Remove Blank Rows Note I'm not affiliated to the makers of the add-on


2

Both copied down might suit: In A1: =unique(_NAME), in C1: =sumif(_NAME,A1,_COUNT)


1

First, use a vlookup function to put data from table 1 alongside table 2. Something like G2 = vlookup(D2, A1:B9999, 2, 0)` Then insert a pivot table with rows of NAME and FEAT1, and value SUM of count.


-2

Fastest solution for me is with a macro. Put a button on the sheet where you want to conserve the already existing data, and link it to the procedure below: Sub MyProgram() Dim MyRange As Range Set MyRange = Range("A1") MyRange = MyRange.Value End Sub Change A1 in the line Set MyRange = Range("A1") to whichever cell's value you want to preserve.


0

You may not have direct option to print comments from the google spreadsheet. But look at this work around and try the steps to take out the comments to print alone: You need you to download the google spreadsheet as .xls or xlsx file in your local machine. 1. Press Ctrl+P, this will open Print option page 2. Select "Page Setup" option 3. Go to 'Sheet' tab ...


1

An array solution could be: =ArrayFormula(IF(LEN(A:A),COUNTIF(Sheet1!A:A&CHAR(9)&Sheet1!B:B,A:A&CHAR(9)&B:B),)) although it might be better to generate the unique counts in the QUERY itself: =QUERY(Sheet1!A1:C10,"select A, B, count(C) where A != '' group by A, B order by A asc label count(C) ''",0) This relies on there being a column C ...


0

Download better touch tool to replace that ugly shortcut with a pretty one. I use the excel shortcut alt up and down :]


2

Try this instead. Code function onOpen() { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for(var i = 0, iLen = sheets.length; i < iLen; i++) { var sheet = sheets[i], range = sheet.getDataRange(); range.sort({column: 1, ascending: true}); } } If you want to sort from a certain row or column, then use this code ...


0

Some finishing touches were applied that have been the subject of another question where there seems to be recognition that a a satisfactory overall conclusion has been reached. This contribution to that from this post was essentially a formula: =query('3 Day - Friday Bus'!A5:I10,"Select I, A,B,C,D,E,F,G,H order BY I desc") to select data from a range ...


0

With the following little script you can do that. Code function mySQL(ref, dates) { var output = []; for(var i = 0, iLen = ref.length; i < iLen; i++) { var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]); for(var j = 0, jLen = dates.length; j < jLen; j++) { var date = new Date(dates[j]); if(date >= ...


0

I hacked around this by, on both spreadsheets by creating a refresh loop using a NOW cell, with both spreadsheets crossreferencing each other's NOW cell. When the original sheet gets appended with a form submission or something, it updates its own NOW cell, and reupdates own IMPORTRANGE cell. The second spreadsheet follows suit, updating its own NOW cell to ...


0

Have you tried isBlank() function?


0

Alternatively, use a combination of the posts above. Click Data → Validation Enter information as needed for your columns Use "List of Items" for your criteria and use the image command to utilise the images of the check box and unchecked box. I.e.: instead of yes,no for your list use =image("http://i.stack.imgur.com/GChKZ.jpg"), ...



Top 50 recent answers are included