Tag Info

Hot answers tagged

3

You can accomplish this with a script: var transSheetName = 'Transactions'; var sumSheetName = 'Summary'; function updateNote(cellDate){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sumSheet = ss.getSheetByName(sumSheetName); var transSheet = ss.getSheetByName(transSheetName); var transSheetLastRow = transSheet.getLastRow(); cellDate = ...


2

I would use a formula to accomplish your goal. Formula =JOIN( CHAR(10), ARRAYFORMULA( QUERY( Transactions!A2:D, "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'" ) & ": " & <=== concatenate QUERY( Transactions!A2:D, "SELECT D WHERE A = date '" & ...


2

In Google New Sheets - being rolled out in April 2014, setting currencies and decimals are split, as you can set the currency first (now hidden at Format -> Number -> More Formats -> More currencies) and then use the Decrease/Increase buttons to change the decimals: Note that I found a bug in that if you decrease decimals on multiple cells, then they will ...


2

This is a known bug in new Sheets whereby the spreadsheet regional settings generate an incorrect output. From the entry on this duplicate issue ticket: If regional settings are set to US then setValue/setValues works properly. If regional settings are German, Polish, Russia and possibly others then there are different results! Polish - ...


2

In new Google Sheets if you select A3:Z20 and Format, Conditional formatting... and enter: =$N3<>"" for Custom formula is with a colour of your choice and Range: A3:Z20 then in the range A3:Z20 every cell should be coloured if the cell in Column N for the corresponding row is not empty. Since such formatting is conditional the IF is not ...


2

Add the following formula in K1: =ARRAYFORMULA(H:H * J:J)) If you have a header in the first row, then do this: =ARRAYFORMULA(H2:H * J2:J)) To avoid the remaining zero's, best is to select a fixed range: =ARRAYFORMULA(H2:H250 * J2:J250)) Reference Google Drive Help: ARRAYFORMULA


1

Perhaps in a helper column (say B assuming data is in A): =if(year(A1)=year(now()),weeknum(A1),"") to identify entries in the latest 10 weeks (by week number) by row and a table in rows1:10 of: =weeknum(now())-10+ROW() for one column (say E) and: =countif(B:B,E1) for another, with all formulae copied down to suit.


1

This is a bit of a crude answer, but I recreated your Drug, Efficacy, and Safety columns and started plugging away at how it should properly count items inside each column. What I noticed: items inside the Efficacy and Safety columns were single cells, with values separated by commas. This means that we can use the SPLIT function to count each individual ...


1

This formula should do the trick for the first row: =INDEX(A$1:D$1, 1, MATCH(MAX(A2:D2), A2:D2, 0)) Drag the formula across to the other rows, and the A2:D2 range should automatically be adjusted to A3:D3, A4:D4 etc. Explanation: The MAX function returns the highest value of the range A2:D2. For row 2, this would be 13%. Next, the MATCH function ...


1

The article you reference says that custom formulae only work in the new sheets. So, have you converted your file to the new Google Sheets? (https://support.google.com/drive/answer/3544847?hl=en&ref_topic=20322)


1

For future reference (as you already have an answer,) you can use FILTER to get you specific rows based on a few conditions. =SUM(FILTER(H:H, A:A=2013, B:B="March")) This would filter out your H column (which I assume are your expenses) for rows that are in the year 2013 and have March as the month indicated in Column B, and then add the numbers together. ...


1

If you're in the summary report sheet and you want to sum something in the expense report sheet, then you need to do the following: SUMIF(expenses!A2:A6, "3", expenses!H2:H6) If your sheet name is Expense Sheet, then you need to write the formula like so: SUMIF('Expense Sheet'!A2:A6, "3", 'Expense Sheet'!A2:A6) If you want to search for values only, ...


1

Another way of doing it is by using the following formula. Formula =IFERROR(QUERY(A2:C8, "SELECT C WHERE B <=" & E2 & "ORDER BY C ASC LIMIT 1"),"") Explained The complete range is set to be the data range. The quasi SELECT statement first filters on items that are less or equal to the selected min qty. Then the items are ordered ascendingly ...


1

Actually, I figured this one out: =if(isblank(C3), "", VLOOKUP(C3, Prices!$A$2:$B$8, 2, true)) Where C3 is the quantity sold, and A2:B8 is the pricing chart range. 2 is the column index (1-indexed), which is in this case unit price (I took out the id column). The true indicates that my list is already sorted. I check if blank so that I don't have 0s all ...


1

There is no native checkbox function, so I would use Data Validation, in combination with COUNTIF (or SUMIF, depending on what you want to count). Check this example sheet. To add Data Validation, first select the cells you want to add it to. Go to Validation in the Data menu Select List of items in the Criteria-drop down and write "Yes, No" in the ...


1

I believe this is what you want. If the comments in the code isn't enough, feel free to ask me for assistance. function compareCells(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet var listSheet = ss.getSheetByName('Sheet15'); //Get sheet "Sheet15" var dataSheet = ss.getSheetByName('Raw ...


1

I've looked in these places on the Google Apps Developer site: Add-ons Release Notes Issue Tracker but wasn't able to find a solution. Therefore I created an feature request in the issue tracker: 3803


1

Without a helper column maybe: =if(match(S2,S:S,0)=match(S2,AF:AF,0),"TRUE",index(AE:AE,MATCH(S2,AF:AF,0))) This is rather inelegant because of the "TRUE" override but basically uses MATCH to find what is effectively the row number to feed to the index function. You have not shown the results you require from your sample data but starting at the top I ...


1

With the following piece of code you can style whatever number you want. Code function onOpen() { SpreadsheetApp.getUi().createMenu('Custom Format') .addItem('Apply format', 'myFormat').addToUi(); } function myFormat() { var ss = SpreadsheetApp.getActive(), oCol = []; var aRange = ss.getActiveRange(), rValues = aRange.getValues(); for(var i=0, ...


1

In Excel these are called Headings and can be hidden or the letters changed to numbers and/or arranged right-to-left rather than the conventional left-to-right but in Google Docs is seems those options are not available. In practice there seems no way to change the Headings in Google Docs. As suggested by @Al E. the conventional approach is to put the ...



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