Tag Info

New answers tagged

2

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


2

Use double instead of single quotations marks i.e. =IF(C1="NULL",B1,C1) References IF - Google Editors Help


0

By using array formulas with "literal" arrays you could append the three ranges in one. =ArrayFormula(Sheet1!A1:D4;{Sheet2!A1:C3,Shee2!D1:D3*-1};Sheet3!A1:D4}) Then you could add the balance column next to the resulting range.


1

This is exactly what VLOOKUP does. =VLOOKUP(A2, Sheet1!A:C, 3, FALSE) searches for A2 in the first column of the range Sheet1!A:C, and returns the content of the 3rd column (relative to the range) in the row that matched. In case of no match, an error #N/A is raised. You can provide a custom error message by wrapping this in IFERROR: ...


1

To convert the calculated value of a cell to a fixed value you could use copy and paste special to paste only the values. To do this automatically you will need a Google Apps Script. For step by step guide, start at Apps Scripts - Tutorials. After learning the basics, take a look to How To Use an Apps Script To “Copy-PasteValue” Dynamic Data In a Google ...


3

When you find yourself writing a bunch of similar IF statement, you probably need FILTER. For example, =JOIN(CHAR(10), FILTER(A1:F1, NOT(ISBLANK(A1:F1)))) joins the content of cells A1 through F1, separating them with linebreaks — but the empty cells are skipped as they fail the condition NOT(ISBLANK(...)).


0

If your are using as a parameter a range that is too large, i.e. whole columns references like A:A, but you don't require the whole range, change it to a smaller range like A1:10. Also you could use functions like FILTER, QUERY, OFFSET, among others, to return small enough ranges.



Top 50 recent answers are included