Tag Info

New answers tagged

0

Apply this Custom formula is: =or(I13=large(unique(I$13:I$41),2),I13=large(unique(I$13:I$41),3),I13=large(unique(I$13:I$41),4)) to Range: I13:I41 and change ColumnI references throughout to E for a separate rule. Note that this applies to the second, third and fourth highest values however many instances of each there may be. So for ...


3

Please try something like: =vlookup(A1,Sheet2!H:I,2,0) where ColumnH in Sheet2 ('other sheet') contains Number and ColumnI of the same row the corresponding unit price.


0

Please try: =index(A:A,randbetween(1,4)) where your list of fruits is assumed to be in ColumnA (and consists of four items). Or without a separate list: =choose(randbetween(1,4),"apple","pear","banana","orange")


1

Can be achieved with Conditional formatting (one rule per colour) where the data is dates: or if text, with Text contains sunday .... saturday.


1

Put a column labels at the top of the column Put in a helper column next to it, and in every cell say =if(ISBLANK(A1),0,1) Then choose Data > Pivot Table Report, and enter the data range as A:B This will create a new tab with the data series you want. You can then create a graph from this. See an example of this approach here: ...


2

According to https://support.google.com/docs/answer/3093620 COUNT function only counts numeric values. Try COUNTA and it should do the job (explained here https://support.google.com/docs/answer/3093991)


0

I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed. I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows ...


1

I'm not sure exactly how you're pulling in the Master Sheet info to the Client Sheet, but to help with some background, when your zap adds a new row to a spreadsheet, it's literally creating a new row and filling that new row with data. This can impact how that data can be automatically retrieved depending on the details of the formulas, scripts, etc you're ...


0

Does Sheet1 have the month, or just a date. If it doesn't have the month, can you make a helper column which contains the month. Then from the Data menu, choose Pivot Table report. Put Name into the Rows, and Month into the Columns (or vice versa), and then put Count of name into the values.


1

Google seems to have fixed this bug. I tried this morning and no problem.


2

This will sum the quantities, per row, for all rows in the range. Formula =ARRAYFORMULA(SUMIF(IF(COLUMN(C2:E4),ROW(C2:E4)),ROW(C2:E4),C2:E4)) Screenshot Example I've created an example file for you: Sum over rows Reference http://stackoverflow.com/a/21804838/1536038


0

Maybe: =left(A1,10)+mid(A1,12,8)


0

It sounds like you have All Formulas checked in the View menu. Go to the View menu or press Ctrl + ` (backtick) to toggle the view on/off - you should then see it alternate between the formulas and the calculated values.


0

IFTTT looks like it can meet your needs. Specifically, this recipe: Add receipts & orders to Receipt Spreadsheet. (There are also other similar recipes.) This recipe is triggered by a new email message with particular words in the subject, and adds a row to a specific spreadsheet. You'd just need to customize it to your specifications.


0

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you ...


2

You can add a Google Script inside the spreadsheet that looks something like this: function save() { var ss = SpreadsheetApp.getActiveSheet(); var threads = GmailApp.search("subject:X"); for (var t in threads) ss.appendRow([threads[t].getFirstMessageSubject()]); }


-1

Switch to the new Google Sheets.


1

I think I just discovered an undocumented formula in the new Google Sheets. @Pnuts was saying the CELL formula didn't work (partially because it's only available in the new Google Sheets), so I copied the code into a new Google Sheet. An error was raised, telling me my (custom) formula accepts only one argument and not two: Renaming the isFormula into ...


0

The simple way is for you to make web app proxying spreadsheet with "Anyone with a link" on Sharing setting of Google Drive. Your web app can authenticate buyers and show caching a link read-only spreadsheet link to them.


-2

this clearly need programming. There is a logical process to follow that is proper to your needs. Anyway if you have the choice, it's better to have one row per dataset (excel is better to have a lot of rows than a lot of columns).


1

Please try with layout as shown. Select A1:C11 (or to suit), insert Line chart with Use row 1 as headers and Use column A as labels:


1

Create a sort key, for example by combining actual values and your custom list orders, such as with lookup tables and concatenation, eg: =VLOOKUP(C2,H:I,2,0)&"|"&D2&"|"&VLOOKUP(A2,F:G,2,0)&"|"&B2 then sort on that column.


2

I think I was trying to achieve the result across spreadsheets before (rather than sheets). If you put a number in A1 of SheetB and in A1 of SheetA Conditional formatting... like so: then if you keep adding 1 to the value in SheetB!A1 the fill colours in SheetA!A1 should toggle red/pale blue. The formulae I chose are of the type: ...


1

IF your sheets are numbered 1 to 30 inclusive (eg up to Sheet30), it may be less tedious to insert: =indirect("Sheet"&column()&"!K36") into ColumnA (say Row10), copy that across to AD, then add that row (say =sum(10:10) ) - or do this on a separate sheet and reference the result from there.


1

This script will do the same (plus a little bit more). Code function myInterpolation(x, y, value) { if(value > Math.max.apply(Math, x) || value < Math.min.apply(Math, x)) { throw "value can't be interpolated !!"; return; } var check = 0, index; for(var i = 0, iLen = x.length; i < iLen; i++) { if(x[i][0] == value) { ...


0

Simplest answer: =Sheet1!K36 + Sheet2!K36 + Sheet3!K36 + Sheet4!K36 + Sheet5!K36 + Sheet6!K36 + Sheet7!K36 + Sheet8!K36 etc There are other answers based on scripting, which you should investigate if you need a repeatable solution (hint - search for 3D summing). But this will be the quickest approach for a one-off.


1

I found a way to do it - there may be a better way, but this is what I came up with: Assuming the data is in A1:B10 and $C$1 contains the key to look for: =FORECAST($C$1, OFFSET(B$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1), OFFSET(A$1,MATCH($C$1,A$1:A$10,1)-1,0,2,1)) In detail: FORECAST does a linear interpolation, but it assumes a straight line. So we ...


3

41824 is Google's date serial number for July 4, 2014 - can be formatted as Date to see this. (The minimum date from today, if not yet reached, would seem always to be the maximum - most recent - date.) Select the cell and click "Format -> Number -> Date".


0

Until we have real vertical test, it's the simplest to leave the text orientation horizontal and spread each heading over several cells using the cell merge function, in a stairlike fashion:


0

OK, some more searching and I found enough hints‡ to find something that works, whether it is officially documented this way, I have no idea. I would love to know where the documentation might be. https://docs.google.com/spreadsheets/d/ID/export?format=tsv&gid=GID The ID is what you would expect, the ID of the sheet's key from the URL. But the other ...


0

Well, one way would be to Find: &char(10) &char(10) &char(10) &char(10) and Replace with &char(10) with Also search within formulae ticked.


0

I'm not sure why this Q is being asked on this site (seems may be rather more about Python coding than use of Google Spreadsheets) but this format (ie as above): https://docs.google.com/spreadsheets/export?id=ID&exportFormat=tsv&gid=0 works for me where ID is replaced by the sheet's key and, if required the 0 on the end with the relevant sheet's ...


1

Maybe: =sum('Mon-D'!$DN$209,'Tue-D'!$DN$209,'Wed-D'!$DN$209,'Thu-D'!$DN$209,'Fri-D'!$DN$209,'Sat-D'!$DN$209,'Sun-D'!$DN$209)


0

Yeah it's too bad Google aren't implementing this. I just put together a little JavaScript bookmarklet for this purpose here. Clicking once inserts a CSS style to hide the headers, clicking again removes the CSS and shows them again. javascript:(function(){var hidecss = "body { margin-left: -45px; margin-top: -47px; } div.grid-table-container { width: ...


0

You can use the option protect sheet or protect range to define permission at the sheet or range level: Note that the two possible permission as of now are editing and commenting:


0

Based on your example, stack B under A and sort. However a more generalised case might be where all the text content (say in A2:B4) is more variable, for which: =if(ISEVEN(row()),indirect("B"&row()/2),indirect("A"&row()/2+0.5)) may suit, if placed in Row3 and copied down to suit.


0

This is the official web site for Google Apps Script: https://developers.google.com/apps-script/ Nowadays, it has an extensive help and tutorial section:


0

With the following piece of code, you can do just that. Code function mySum(range) { var sum = 0, counter = 0, c = 0, output = [], index; for(var j = 0, jLen = range.length; j < jLen; j++) { if(range[j].indexOf('') !== 0) { sum += range[j][0]; counter++; } else { index = c * (j - counter); output[index]= sum; ...


0

I am assuming that you need a one-off solution to manipulate the data in one spreadsheet, not a repeatable solution that doesn't need any manual copy/pasting. Please ignore the following if that'd not true. Insert two helper columns (they can be temporary if you like), one on the left of the first column, the other on the right of the second column - like ...


0

Tom Horwood's answer works great, but only after correcting an important error in his code. All references to LIST_DATA elements should be made using the index j (not i). I don't have sufficient karma to comment on Tom's post so here's the fully monty: Below is the corrected code. I've also changed the name of the LIST_DATA elements so they are a little ...


0

Use the following code to change the date notation. Code var curDate = Utilities.formatDate(new Date(), "GMT+1", "mm/dd/yyyy") References formatDate(date, timeZone, format) date notaions


1

Use the following code. Code function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var shs = ss.getSheets(), num = shs.length; var sheetnames = []; for(var i=0; i<num; i++) { sheetnames.push(shs[i].getSheetName()); } return ss.toast(sheetnames, "Greetings!", 300); } Explained The sheets are zero based (as arrays are). ...


0

Applying meaning to the row which your data is in is simply a bad idea. I know it's tempting, but it leads to problems long term. If you need to know how many rows you have in a particular block of data, then add a helper-column on one side of your data, call it ID or Number or something meaningful in your context, and put the row number in there.


2

I'll ignore "Does anyone else find this frustrating?" (see 5th bullet point in first set). "What are possible solutions?" Add an index column (A is best for this) say containing =row() copied down (and then convert to values or sorting could cause problems). Maybe format that column to make the index values more prominent than the headings for rows. "Is it ...



Top 50 recent answers are included