3
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 ...
- 4,980
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 ...
- 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"...
- 17.1k
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 ...
- 44.6k
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.
...
- 17.4k
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 ...
- 44.6k
2
votes
How to organize and add multiple tags to a list?
It's not an easy task to do in a spreadsheet.
But one simple solution could be to make a new column that makes the tags filterable, using an expression like
=CONCATENATE("|", REGEXREPLACE(B2; " *, ...
- 306
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)
- 144
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 ...
- 4,980
2
votes
How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty
The solution is to apply conditional formatting to the C2:L range with the custom formula:
=and($B2<>"",C2:L2="")
- 2,338
2
votes
Accepted
Does importrange not work on Google Sheets embedded XLSX files?
The importrange() function can only read data from Google Sheets and therefore cannot read data from Microsoft Excel files, even though you can view and edit such files with Google Sheets. I do not ...
- 13.2k
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:...
- 2,338
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)}
- 12.6k
1
vote
A way to "lock" and "unlock" a Google Sheet's contents
An editor of a spreadsheet cannot modify sheets or ranges that have been protected from edits. However, if they are the owner of the spreadsheet, they can always edit any cell directly. The exception ...
- 13.2k
1
vote
Google Sheets Formula Not Working in Excel
Turns out Excel does not like A5:A. A needs to be defined. A5:A1000 will work.
- 11
1
vote
How can I highlight the source cells in a formula (e.g. =SHEETA!$E$568 )
Try this script.
To access it, either put it in a custom menu or create a shortcut.
function jumpto() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var ui = SpreadsheetApp.getUi();
// ...
- 5,520
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 ...
- 87
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(...
- 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....
- 4,980
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 =...
- 13.2k
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:
=...
- 791
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 ...
- 5,520
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 ...
- 44.6k
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")
- 791
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 ...
- 3,272
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/...
- 207
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.
- 17.4k
Only top scored, non community-wiki answers of a minimum length are eligible