3

Just like Microsoft's VBScript, Google has its own language - Apps Script. It lets you automate sheets with a Javascript-like syntax, should be easy if you know Javascript. Tutorial and documentation from Google: https://developers.google.com/apps-script To use it, open your Google Sheet file > Tools > Script Editor...


2

This is a job for the INDIRECT function. For the sum in A8, use =SUM(INDIRECT("R2C" & B8 & ":R5C" & B8; FALSE)) Likewise, for the sum in A9, use =SUM(INDIRECT("R2C" & B9 & ":R5C" & B9; FALSE)) Explanation: INDIRECT lets you construct cell addresses from dynamic input, e.g. from other cells. It supports both A1 notation (as in A2),...


2

Short answer The script should be wrote by and end-user/developer. Explanation Google Sheets doesn't include a way to automatically convert VBA code to Google Apps Scripts. By the other hand not all the VBA statements, classes, methods, etc. have an equivalent on Google Apps Script and not all the Excel features have an equivalent on Google Sheets, so ...


2

Seems your Excel is set to the 1904 date system (more likely for Mac users than PC users). This is an Excel option. Dates in Excel are index numbers that by convention start either in 1900 or 1904. The setting may be changed in Excel under Options > Advanced > When calculating this workbook.


2

Here's a shorter option: =IF(COUNTA(B2:F2) < 5, "", SUM(B2:F2)) The 5 is the number of values you are looking for. (The number of days of the week you have listed)


2

Google Sheets has hard-and-fast limitations. One of them is that no spreadsheet may have more than five million cells total among all sheets/tabs. If you are trying to upload 1M rows, and those rows each contain more than 5 cells each (which is likely), you'll have exceeded this number (in fact, 1M rows with 5 cells each completely maxes the space limitation)...


1

With @Rubén's insight into how Google Sheets was inheriting border-applying conditional formatting rules from Excel (without a way to access or edit those rules), I was able to figure out a different conditional formatting rule in the original Excel document that achieves the same goal and translates properly to Google Sheets. For what it's worth, this is a ...


1

Check this sheet (Make a copy for full access) Note the Helper Pivot tab, which is referenced by formulas in Expenses tab. You can hide that tab in the final spreadsheet. The formula =iferror(GETPIVOTDATA("SUM of $",'Helper Pivot'!$A$1,"Category",$A3,"Date - Month",text(D$2,"MMM")),0) can be copied/filled right or down ...


1

If your times in Col A are hour-minute as shown (i.e., no seconds): =QUERY(A:B, "Select A, SUM(B) WHERE A Is Not Null GROUP BY A LABEL SUM(B) ''") This assumes your values start in A1:B1 (i.e., that you have no headers). A longer but more flexible and easily transferable version of the above: =QUERY({A:B}, "Select Col1, SUM(Col2) WHERE Col1 Is ...


1

You should not blindly trust that each name will continue to be at the same row number on every sheet. Use a vlookup() or filter() instead. Try this: =sum( iferror( filter(Earned!B$2:ZZ, Earned!A$2:A = A2) ) ) - sum( iferror( filter(Used!B$2:ZZ, Used!A$2:A = A2) ) )


1

Please use the following FLATTEN formula =FLATTEN(A2:C4) OR ={"Questions";FLATTEN(A2:C4)}


1

Rich-Text formatting is not an OPT in/out / turn on/off feature. It's always turned on. To use it: Put the cell in edit mode Type the text Select the text part to apply bold, italics, color Apply the desired format by using the Google Sheets toolbar or keyboard-shortcuts like Ctrl+b for bold, Ctrl+i for italics, Ctrl+k to insert a link, etc. Put the cell in ...


1

You need to use TEXT, instead of TO_TEXT: =TEXT('90diaspessoas'!A16,"dd-mm-yyyy" ) As long as underlying date number is correct, This will work. OR Set formatting in '90diaspessoas'!A16 correctly to DD-MM-YYYY. Then, TO_TEXT will work. FINAL CONCATENATED FORMULA: =TEXT('90diaspessoas'!A16,"dddd dd-mm-yyyy")


1

Try using the EDATE() formula. Since it adds months you'll need 240. Something like this: =ARRAYFORMULA(EDATE(A2:A2000,240)) Then paste values. Alternatively, you could use this formula: =ARRAYFORMULA(DATE(YEAR(A2:A2000)+20,MONTH(A2:A2000),DAY(A2:A2000)))


1

The correct formula is: =IF(G15=TRUE,1,0) That is, drop the quotes around TRUE.


1

An option is to output the file name as well as the HYPERLINK() function. This should allow you to: sort the output, lookup the file name and return hyperlink (or vice versa, according to your preference) In this snippet, str is the hyperlink and str2 is the name of the file. The final line of code is simply setValues rather than setFormulas while (files....


1

From https://developers.google.com/chart/interactive/docs/gallery/trendlines#exponential-trendlines Note: Unlike linear trendlines, there are several different ways to compute exponential trendlines. We provide only one method right now, but will support more in the future, and so it's possible that the name or behavior of the current exponential ...


1

You need following formulas: MAXIFS and MINIFS. =MAXIFS(A2:G2,$A1:$G1,"bar") =MINIFS(A2:G2,$A1:$G1,"bar")


1

I'm no Excel guru, but I think this page on the CommCare Help Site goes through the steps to do at least some of what you're talking about: https://confluence.dimagi.com/display/commcarepublic/Tutorial%3A+Create+an+Excel+Dashboard. The key might be starting at "Step 4: Name your raw data for easy reference later" where is shows you how to mark all rows in ...


1

What's the equivalent of the SEP metadata in Google Sheets? There isn't one. is there an all around metadata which could work both on MS Excel and Google Sheets No.


1

Short answer Apply "real" border lines. Explanation The "default borderlines" in Excel and other spreadsheets application are named grid lines. They are a visual aid, not real formatting.


1

As far as I am aware, this is not possible, given Excel's incompatibility with Google Apps Script. There is an alternate and only slightly less efficient solution, though: each time you want to analyse the results of your Form, simply download the connected Google Sheet as an Excel spreadsheet, open the downloaded spreadsheet in Excel, and do your analyses ...


1

I have tested 2 tools allowing to export trello data to Excel: Directly in Trello, you can subscribe to their Business Class package and export all cards from all boards in one operation. See their help page here. https://help.trello.com/article/747-exporting-data-from-trello-1 You can use the add-on Bridge24 for Trello. That tool allow you to select ...


1

Go to Board Menu > Share, Print, & Export (this will export the data to a JSON file) Use this JSON to CSV converter to convert the JSON file to CSV format. Open the file up in Excel. As recommended by Trello: http://help.trello.com/article/924-making-sense-of-trellos-json-export


1

As Gmail doesn't preserve formatting, your options may be to: Copy the file to Google Drive and reference that using the "Insert files using Drive" option Insert a screenshot of the relevant portion of the sheet within the email, if you're particular about formatting and the data isn't a lot


1

Please try: Find: (.+)\) Replace with: $1,0) Search: This sheet (or to suit) Search using regular expressions Also search within formulae Replace all.


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