3 votes

Excel to Google Sheets Script

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 ...
user avatar
  • 76
2 votes
Accepted

How do I calculate a sum based on a column number which is the value of another cell?

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"...
user avatar
2 votes

How convert VBA to Google Sheet script (to lock columns based on data)

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 ...
user avatar
  • 42k
2 votes
Accepted

Exporting date fields from Cognito Forms to Excel on Mac transforms date by 4 years

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. ...
user avatar
  • 16.8k
2 votes

In Google Sheets or Excel, how can I write a formula to SUM() a range based on the criteria that none of the respective cells are blank?

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)
user avatar
2 votes

Rich text formatting in a Google Sheets cell

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 ...
user avatar
  • 42k
2 votes

What's the best way to upload a million row spreadsheet as Google spreadsheet?

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 ...
user avatar
  • 4,587
2 votes
Accepted

Select full column in Google Sheets by [@column]

I recommend that you use named ranges. For instance: 1.) Select all of Column A by clicking on the "A" at the top of that column. 2.) Select Data > Named ranges. 3.) Enter a descriptive ...
user avatar
  • 4,587
1 vote

Automatically Color Series In Chart

The following answer was developed piecemeal and many elements could have been combined to create a more efficient process. However, each of the functions will run standalone and this may assist the ...
user avatar
  • 4,768
1 vote
Accepted

Check if any cells in one column fall between cells in different columns in Google Sheets

Try this formula: =isbetween(A3:A6,B2:B5,C2:C5) you could conditional format column A (A3:A1000) with custom formula: =and(A3<>"",isbetween(A3,B2,C2))
user avatar
  • 1,276
1 vote

How do I put the if function inside the lookup? - Daily Routine sheet

I'd use Index and Filter (Filter () creates a list of upcoming tasks, Index () takes only the first of those) =index(Filter(A2:A,timevalue(B2:B )>=timevalue(now())),1,1) to get something like this:...
user avatar
  • 1,276
1 vote

Lookup between two dates in Google Sheets

In Sheet1 B2 enter: =index(ifna(vlookup(A2:A,{Sheet2!B2:B,Sheet2!A2:A},2))) Update: ={"Sprint"; flatten(index(iferror(--query(if( (transpose(indirect("A2:A"&max(row(A:A)*(A:A&...
user avatar
  • 222
1 vote
Accepted

Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?

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 ...
user avatar
  • 77
1 vote

How do I get data from the 'Statement' sheet to automatically sort into the 'Expenses' sheet?

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(...
user avatar
  • 161
1 vote
Accepted

Sheets or Excel - sum values in column a for each unique date and time in col b

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....
user avatar
  • 4,587
1 vote
Accepted

Keeping referenced data organized after sorting

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 =...
user avatar
  • 6,798
1 vote

How to move “answers” rows into under column “questions”?

Please use the following FLATTEN formula =FLATTEN(A2:C4) OR ={"Questions";FLATTEN(A2:C4)}
user avatar
  • 11.8k
1 vote

Format Google Analytics Date

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 ...
user avatar
  • 3,068
1 vote

I wrongly set the date of 2000 cells. I want to add 20 years of those cells. How?

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: =...
user avatar
  • 760
1 vote

IF Statement works in Excel but not G Sheet?

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

Creating Hyperlinks of Google Drive Items in Alphabetic Order

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 ...
user avatar
  • 4,768
1 vote

Why do Google Sheets and Excel give different exponential regressions?

From https://developers.google.com/chart/interactive/docs/gallery/trendlines#exponential-trendlines Note: Unlike linear trendlines, there are several different ways to compute exponential ...
user avatar
  • 42k
1 vote
Accepted

How to get maximum value from rows, only from specific columns

You need following formulas: MAXIFS and MINIFS. =MAXIFS(A2:G2,$A1:$G1,"bar") =MINIFS(A2:G2,$A1:$G1,"bar")
user avatar
  • 760
1 vote

How to Automate Adding Rows of Analysis for Excel Dashboard Feed Integration

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/...
user avatar
1 vote

What's the equivalent of the SEP metadata in Google Sheets?

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.
user avatar
  • 16.8k
1 vote

Excel spreadsheet into body of Gmail message without losing formatting

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 ...
user avatar
  • 42k
1 vote

Is it possible to link a Google Form to a desktop Excel sheet so that when someone fills in the form the data goes into the Excel sheet?

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 ...
user avatar
  • 1,678
1 vote

Export Trello data to Excel

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 ...
user avatar
1 vote

Export Trello data to Excel

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 ...
user avatar
  • 161

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