Tag Info

Hot answers tagged

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.


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".


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 ...


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: ...


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


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()]); }


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)


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: ...


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 ...


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.


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)


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

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

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) { ...


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 ...


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). ...


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 ...


1

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



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